How to select all data corresponding to the 3 latest dates for each ID?

Question

ID   | DATE       | OTHER | OTHER 
----------------------------------
ID45 | 8/22/2020  |       |
ID45 | 9/12/2020  |       |
ID45 | 12/13/2020 |       |
ID45 | 7/14/2020  |       |
ID56 | 3/15/2020  |       |
ID56 | 2/19/2020  |       |
ID56 | 9/24/2020  |       |
ID56 | 5/18/2020  |       |
ID72 | 7/20/2020  |       |

I would like to see all the data (all columns) related to the last 3 dates of an ID The result would be in this case:

ID   | DATE       | OTHER | OTHER 
----------------------------------
ID45 | 12/13/2020 |       |
ID45 | 9/12/2020  |       |
ID45 | 8/22/2020  |       |
ID56 | 9/24/2020  |       |
ID56 | 3/15/2020  |       |
ID56 | 2/19/2020  |       |

Could you please help me?

https://stackoverflow.com/questions/1895110/row-number-in-mysql — Amira Bedhiafi, Mar 03 '21 at 21:06

score 0 · Accepted Answer · answered Mar 03 '21 at 21:05

0

You can use window functions:

select t.*
from (select t.*,
             row_number() over (partition by id order by date desc) as seqnum
      from t
     ) t
where seqnum <= 3;

answered Mar 03 '21 at 21:05

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Mar 03 '21 at 21:08

0

SELECT * FROM (
    [ID],[Date]
    ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Date] DESC) AS rn
FROM YourTable) AS A
WHERE A.rn<=3

answered Mar 03 '21 at 21:08

Amira Bedhiafi

8,088
6
24
60

score 0 · Answer 3 · answered Mar 03 '21 at 21:14

0

If in case you don't want to use windowed function, simpler solution is:

SELECT *
    FROM TABLE
        WHERE DATE IN (SELECT TOP 3 DATE FROM TABLE ORDER BY DATE DESC)
    ORDER BY DATE DESC

answered Mar 03 '21 at 21:14

Akash Joshi

1
3

Top is sqlserver mysql equivalent is limit – P.Salmon Mar 04 '21 at 07:25

How to select all data corresponding to the 3 latest dates for each ID?

3 Answers3