Skip to content
Advertisement

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

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?

Advertisement

Answer

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;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement