CODEWITHSUNDEEP
sqlms-access
kattouf
kattouf

Reputation: 59

SQL: Joining two tables and selecting top N in group from one table

I am using MsAccess and will upsize to sql soon.

I have this scenario:

Table 1 : UserID Name

Table 2 : GradeID Date Grade UserID

What I want to do is join both tables in a query or report to list all users alphabetically and their latest 2 grades. I looked up 'top n in group' solutions but can't figure them out or many didn't work. Currently I use Inner Join but that list all grades

Solution should look like:

User1 John Grade30 date grade
user1 John Grade29 date grade
user2 bob  Grade41 date grade
user2 bob  Grade13 date grade

thank you!

Upvotes: 2

Views: 698

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

This is tricky in MS Access. One method uses a correlated subquery:

select t1.*, t2.grade, t2.date
from table1 as t1 inner join
     table2 as t2
     on t1.userId = t2.userId
where t2.date in (select top (2) tt2.date
                  from table2 as tt2
                  where tt2.userId = t2.userId
                  order by tt2.date
                 );

Upvotes: 0

Related Questions