select maximum date of multiple dates-CodePudding

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')

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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' */ ;