Getting Max Date for each group using MYSQL

Question

I have a Invoice table in MYSQL which have below data types. INVOICE : ID VARCHAR(20) DATEADD TEXT STATUS VARCHAR(10) Data looks :

   ID           DATEADD        STATUS
'A0011'  '04/01/2018 11:58:31'  'N'
'A0011'  '31/05/2019 10:02:36'  'N'
'B0022'  '04/01/2018 11:58:31'  'N'
'B0022'  '31/05/2019 10:02:36'  'N'
'B0022'  '30/04/2020 19:44:36'  'N'

Script :

SELECT  ID,MAX(DATEADD)DATEADD,STATUS FROM (
SELECT  ID ,STR_TO_DATE(DATEADD, '%d/%m/%y')DATEADD,STATUS FROM Invoice  WHERE  STATUS = 'N' )T
 GROUP BY  ID, Status;

But I'm not getting MAX date for the above data.

Required Out Put :

   ID           DATEADD        STATUS
'A0011' '31/05/2019 10:02:36' 'N'
'B0022' '30/04/2020 19:44:36' 'N'

Are you using `MySQL 8+` version ? And please never store dates on text fileds — Ergest Basha, May 10 '22 at 09:24

Ergest Basha · Accepted Answer · 2022-05-10T09:44:32.437

With MySQL 8+ you can use ROW_NUMBER

with cte as(
select *,row_number() over(partition by ID order by STR_TO_DATE(DATEADD, '%d/%m/%Y %H:%i:%s') desc) as row_num
from Invoice 
) select ID,DATEADD,STATUS
from cte
where row_num =1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=150299f52a7bfa8d06f42d723c648ed6

On Older MySQL try:

 SELECT ID,  
         DATEADD,
         status
FROM Invoice in1 where (ID,STR_TO_DATE(DATEADD, '%d/%m/%Y %H:%i:%s')) in ( select ID,                                                  
                                                                                  MAX(STR_TO_DATE(DATEADD, '%d/%m/%Y %H:%i:%s'))
                                                                           from  Invoice
                                                                          GROUP BY ID 
                                                                          );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2d0344c1eb99de9e00c6410f5b734133

Getting Max Date for each group using MYSQL

1 Answers1