MS-Access SELECT TOP N subquery SQL

Question

have attempted this query multiple times, feel like I am just missing something simple.

this example: aggregation subquery with top N is close to what I am looking to accomplish, however rather than averaging the distance I only want to return the top N distances and shopIDs.

in my data, value is analogous to a numeric score, with a higher value being bad. my objective is to obtain a list showing the top 2 worst scoring SubTypeNames and their score for each StationId.

my sample table: (Composite PK: StationId and SubTypeId)

StationId | SubTypeId | SubTypeName | Value

STA001 | 1 | TypeA | 4.63
STA001 | 2 | TypeB | 2.57
STA001 | 3 | TypeC | 3.54
STA001 | 4 | TypeD | 4.19
STA001 | 5 | TypeE | 1.61
STA002 | 1 | TypeA | 5.12
STA002 | 2 | TypeB | 4.57
STA002 | 3 | TypeC | 1.28
STA002 | 4 | TypeD | 1.27
STA002 | 5 | TypeE | 3.33
STA003 | 1 | TypeA | 4.51
STA003 | 2 | TypeB | 4.51
STA003 | 3 | TypeC | 5.63
STA003 | 4 | TypeD | 1.28
STA003 | 5 | TypeE | 3.25

this pattern repeats for 50+ stations.

my UPDTAED output: (from query shown below)

StationId | SubTypeName | Value

STA001 | TypeA | 4.63
STA001 | TypeD | 4.19
STA002 | TypeA | 5.12
STA002 | TypeB | 4.57
STA003 | TypeC | 5.63
STA003 | TypeA | 4.51
STA003 | TypeB | 4.51

my most recent attempt:

SELECT c.StationId, c.SubTypeName, c.Value
FROM [TT: StationSubType Values] As c
WHERE c.SubTypeId IN
(SELECT TOP 2 c2.SubTypeId FROM  [TT: StationSubType Values] As c2 WHERE c2.StationId = c.StationId
ORDER BY c2.Value DESC)
ORDER BY c.StationId ASC, c.Value DESC;

UPDATE: I have the above query working ALMOST as expected. The issue that remains results from duplicate values at the same station as someone foreshadowed in their comment. For the purposes of this query I just need TOP N, is there a way to just take the first SubTypeId of n results with equal values?

any help would be greatly appreciated. thanks!

Answer 1

Try this :

SELECT  c.SubTypeName,
        c.StationId,
        c.Value

FROM [Sample] AS c
WHERE c.SubTypeId IN

            (SELECT TOP 2 c2.SubTypeId 
             FROM  [Sample] As c2 
             WHERE c2.StationId = c.StationId
             ORDER BY c2.Value DESC, c2.subtypeid)

ORDER BY c.StationId ASC, c.Value DESC

I'm not sure if this solution will affect any other records in your Db (because you are dealing with more than I), but working with the sample data you provided this seemed to work.