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.

CodePudding user response：

If your mysql version support the window function, we can try to use ROW_NUMBER window function to get each ID latest DATEADD then compare the STATUS

SELECT *
FROM (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATEADD DESC) rn
 FROM INVOICE
) t1
WHERE rn = 1 AND STATUS = 'N'

sqlfiddle

if your MySQL version didn't support the window function we can try to use correlated subquery

SELECT *
FROM (
 SELECT *, (SELECT COUNT(*) 
            FROM INVOICE tt 
            WHERE tt.ID = t1.ID AND tt.DATEADD > t1.DATEADD) rn
 FROM INVOICE t1
) t1
WHERE rn = 1 AND STATUS = 'N'

sqlfiddle

CodePudding user response：

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.

CodePudding user response：

We can use ORDER BY and LIMIT 1 to get the row that we want without using any functions, sub-queries, CTE etc.
Thank you to D-Shih for the test schema. If we want the maximum date with status 'N' for each ID we can use the second query.

SELECT
  ID,
  DATEADD,
  STATUS
FROM INVOICE
ORDER BY 
  STATUS DESC,
  DATEADD DESC
LIMIT 1;

ID    | DATEADD    | STATUS
:---- | :--------- | :-----
A0011 | 2019-05-31 | N     

SELECT
  ID,
  MAX(DATEADD) AS DATEADD,
  STATUS
FROM INVOICE
WHERE STATUS = 'N'
GROUP BY ID
ORDER BY ID;

ID    | DATEADD    | STATUS
:---- | :--------- | :-----
A0011 | 2019-05-31 | N     
B0022 | 2019-05-31 | N     
C0033 | 2018-01-04 | N     

db<>fiddle here