0

I want to get the top 2 dates for each ID but having trouble solving the query

ex.

ID Name Date
1 A 1/2/22 1:07pm
1 A 1/2/22 1:06pm
2 A 1/2/22 1:05pm
2 A 1/2/22 1:04pm
1 A 1/2/22 1:03pm
2 A 1/2/22 1:02pm
3 A 1/2/22 1:01pm
3 A 1/2/22 1:00pm

What I tried: SELECT DISTINCT TOP 2 T1.ID, T2.Name, T1.Date FROM T1 INNER JOIN T2 ON T1.ID = T2.ID WHERE ((T2.Name) = [Name: ]) --type in the name ORDER BY T1.Date DESC;

What I expected to happen:

ID Name Date
1 A 1/2/22 1:07pm
1 A 1/2/22 1:06pm
2 A 1/2/22 1:05pm
2 A 1/2/22 1:04pm
3 A 1/2/22 1:01pm
3 A 1/2/22 1:00pm

What I actually got:

ID Name Date
1 A 1/2/22 1:07pm
1 A 1/2/22 1:06pm

Please anyone let me know what I did wrong and what I'm missing, thank you

MT0
  • 143,790
  • 11
  • 59
  • 117
york
  • 1
  • 1
  • 2
    Common topic. Does this answer your question? [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) or https://stackoverflow.com/questions/3481916/access-top-n-in-group – June7 Nov 03 '22 at 02:29

0 Answers0