select top x by group in ms access-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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.