select maximum date of multiple dates

Question

How do I get the number of rows of maximum creation dates?

Example data:

id|code|transaction_date   |amount|record_status|creation_date
1 |0001|2021-12-10 00:00:00| 10.00|D            |2021-12-10 00:00:00
2 |0001|2021-12-10 00:00:00| 10.00|D            |2021-12-11 10:00:00
3 |0002|2021-12-11 00:00:00| 10.00|D            |2021-12-11 00:00:00
4 |0002|2021-12-11 00:00:00| 10.00|D            |2021-12-12 10:00:00

I want to get this:

id|code|transaction_date   |amount|record_status|creation_date
2 |0001|2021-12-10 00:00:00| 10.00|D            |2021-12-11 10:00:00
4 |0002|2021-12-11 00:00:00| 10.00|D            |2021-12-12 10:00:00

I am trying this

SELECT * FROM table1
WHERE (SELECT max(creation_date) from table1 WHERE DATE(transaction_date) = '2021-12-10')
AND record_status = 'D';

But I won't be able to select a range of date like I want IN ('2021-12-10', 2021-12-11')

Does `(code,creation_date)` is defined as unique? If not then what output do you need in the case when more than one row contains the same maximal `creation_date`? — Akina, Dec 16 '21 at 13:45
Are you after the maximum creation date for each code? if so `Lateral` is the table function you're looking for: https://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql or a coorleated subquery with a limit or an analytic. This could also be accomplished by assigning a row number for each partition of code ordering by creation date descending and then limiting by the row number 1 in an outer query. Take your pick... each works offers similar performance; and depending on data/structure some are slightly faster. — xQbert, Dec 16 '21 at 13:48

Ergest Basha · Answer 1 · 2021-12-16T20:13:19.983

2

Try:

CREATE TABLE  table1 (
 id int,
 code int,
 transaction_date datetime,
 amount decimal(10,2),
 record_status varchar(2),
 creation_date datetime );

INSERT INTO table1 VALUES

(1,0001,'2021-12-10 00:00:00',10.00,'D','2021-12-10 00:00:00'),
(2,0001,'2021-12-10 00:00:00',10.00,'D','2021-12-11 10:00:00'),
(3,0002,'2021-12-11 00:00:00',10.00,'D','2021-12-11 00:00:00'),
(4,0002,'2021-12-11 00:00:00',10.00,'D','2021-12-12 10:00:00'),
(5,0003,'2021-12-10 00:00:00',10.00,'D','2021-12-11 10:00:00'),
(6,0003,'2021-12-10 00:00:00',10.00,'D','2021-12-16 10:00:00');



SELECT t1.*
FROM table1 t1
WHERE t1.creation_date = ( SELECT  MAX(t2.creation_date)
                           FROM table1 t2
                           WHERE t2.code = t1.code
                         );

Result:

id    code    transaction_date    amount  record_status   creation_date
2 1   2021-12-10 00:00:00 10.00   D   2021-12-11 10:00:00
4 2   2021-12-11 00:00:00 10.00   D   2021-12-12 10:00:00
6 3   2021-12-10 00:00:00 10.00   D   2021-12-16 10:00:00

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3b59835d81c67bc7b366ecce815ce67d

edited Dec 16 '21 at 20:13

answered Dec 16 '21 at 13:39

Ergest Basha

7,870
4
8
28

Possible to only select a range of transaction_date? – Alvin Dec 16 '21 at 13:41
1

@Alvin You could add the `transaction_date` in a `where` condition inside the subquery of getting the max creation time. Try with your data and let me know if it helps – Ergest Basha Dec 16 '21 at 13:43
1

You need a correlated subquery to ensure the correct result. – jarlh Dec 16 '21 at 13:47
This works for the example data shown, but it won't work for all data, because another `code` may have a date matching one of those returned by the subquery even though it isn't the max date for that `code`. – Bill Karwin Dec 16 '21 at 14:41

PZBird · Answer 2 · 2021-12-16T14:04:08.347

1

For using a range you can use between, like:

SELECT * FROM table1
WHERE creation_date IN (
SELECT max(creation_date) from table1
WHERE record_status = 'D'
   AND creation_date between '2021-12-11'
   AND '2021-12-13'
group by DATE(transaction_date))

Example

edited Dec 16 '21 at 14:04

answered Dec 16 '21 at 13:52

PZBird

321
2
8

score 0 · Answer 3 · answered Dec 16 '21 at 13:43

How about something like?

SELECT `table1`.*
FROM `table1`
INNER JOIN (
    SELECT `code`, MAX(`creation_date`) AS `max_date`
    FROM `table1`
    WHERE `record_status` = 'D'
    GROUP BY `code`
) `latest`
    ON `table1`.`code` = `latest`.`code`
    AND `table1`.`creation_date` = `latest`.`max_date`

This uses a derived table to get the max date per code.

score 0 · Answer 4 · answered Dec 16 '21 at 13:47

SELECT *
FROM table t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM table t2
                   WHERE t1.code = t2.code
                     AND t1.creation_date < t2.creation_date
                  /* AND t2.record_status = 'D' 
                     AND t2.DATE(transaction_date) = '2021-12-10' */ )
/* AND record_status = 'D'
   AND DATE(transaction_date) = '2021-12-10' */ ;

select maximum date of multiple dates

4 Answers4