I have the following MS Access DB schema:
I want to select rows from Items table ordered by Items.score so that there are at most Group.top_count rows for each group.
For example I have the following data in the tables:
I want to select top 2 items for group #1 and top 1 item for group #2. So the result must contain rows 1, 2 and 5.
There was a similar question at DBA stackexchange, but about SQL Server. So all answers used SQL Server syntax and I couldn't adapt it to work on MS Access.
If there were a constant number per group, you could do:
select i.* from items as i inner join groups as g on i.group_id = g.id where i.id in (select top 2 i2.id from items i2 where i2.group_id = i.group_id order by i2.score desc );
Instead, you will need to enumerate the values and this is expensive in MS Access:
select i.* from (select i.*, (select count(*) from items i2 where i2.group_id = i.group_id and (i2.score < i.score or i2.score = i.score and i2.id <= i2.id ) ) as seqnum from items as i ) as i inner join groups as g on i.group_id = g.id where i.seqnum <= g.top_count;
This logic implements the equivalent of
row_number(), which is the right way to solve this problem (if your database supports it).