'Getting Max Date for each group using MYSQL [duplicate]

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


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions