select top x by group in ms access

Question

I have a dataset like this

A  B  Group  Index
1  3   G1        25
1  3   G1        23
1  3   G1        99
4  5   G2        6
4  5   G2        8
4  5   G2        10
4  5   G2        2
4  5   G2        4

Ideal output is to pick top x rows for each group with a descending order for Index

Group  Index
G1      23
G2      6
G2      8
G2      2
G2      4

For each distinct value in Group, values of A and B are identical for all rows.
B indicates how many rows in the dataset. E.g. we have 3 rows for G1.
A indicates how many rows needed for output. E.g. in the output only one row with lowest value of Index exists for G1 and 4 rows for G2

Can anyone shed some light on how to deal with this?

Is it possible to add a `row_number` for each row by group, then filter the records with `row_number <= A` ? But I have no idea how to add a `row_number` field in ms access. — Lovnlust, Jan 11 '22 at 04:23
Does this answer your question? [Top 3 per group including 0](https://stackoverflow.com/questions/33374489/top-3-per-group-including-0) — Darren Bartrup-Cook, Jan 11 '22 at 08:38

tinazmu · Accepted Answer · 2022-01-11T05:39:31.717

1

MsAccess can have a subquery as its select expression, which can be used to produce a limited rownumber:

SELECT *
FROM (
SELECT TblA.*, 
   (SELECT count(*) from Sheet1 TblB 
    where tblB.GROUP=tblA.GROUP and tblB.Index<=tblA.Index) as ROWNO
FROM Sheet1 TblA) Step1
where Step1.ROWNO<=Step1.A

Biggest caveat is that if two rows of the same group can have the same Index value.

edited Jan 11 '22 at 05:39

answered Jan 11 '22 at 05:34

tinazmu

3,880
2
7
20

score 1 · Answer 2 · answered Jan 11 '22 at 08:57

A single subquery will do:

SELECT 
    YourTable.Group, 
    YourTable.Index
FROM 
    YourTable
WHERE 
    (Select Count(*) From YourTable As T 
    Where T.Group = YourTable.Group And T.Index <= YourTable.Index) <= [A]
ORDER BY 
    YourTable.Group, 
    YourTable.Index DESC;

This assumes, that the values of Index are unique for each group.

select top x by group in ms access

2 Answers2