Need to Pick Max Date when status = N otherwise No in MYSQL

Question

I have a table which have records like this

   ID           DATEADD        STATUS
'A0011'  '04/01/2018 11:58:31'  'C'
'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'  'C'
'C0033'  '04/01/2018 11:58:31'  'N'
'C0033'  '30/05/2019 06:02:36'  'C'
'C0033'  '29/04/2020 05:44:36'  'C'

I'm trying to get the Max Date for each ID which have STATUS = 'N'. If I get MAX DATE and STATUS = 'C' then I don't want that record.

Output :

   ID           DATEADD        STATUS
'A0011'  '31/05/2019 10:02:36'  'N'

SCRIPT :

SELECT I.* FROM INVOICE I
INNER JOIN (
Select ID,MAX(DATEADD)DATEADD,STATUS FROM INVOICE WHERE STATUS = 'N'
GROUP BY ID,STATUS) O
ON I.ID = O.ID AND O.DATEADD = I.DATEADD 

But I'm not able to get desired output.

Answer 1

You can use NOT EXISTS:

SELECT i1.* 
FROM INVOICE i1
WHERE i1.STATUS = 'N'
  AND NOT EXISTS (
    SELECT 1
    FROM INVOICE i2
    WHERE i2.ID = i1.ID 
      AND STR_TO_DATE(i2.DATEADD, '%d/%m/%Y %H:%i:%s') > STR_TO_DATE(i1.DATEADD, '%d/%m/%Y %H:%i:%s')
  );

If the column's DATEADD data type is DATETIME or TIMESTAMP the last condition would be simpler:

...AND i2.DATEADD > i1.DATEADD

See the demo.