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!

No, in this case Value contains a numeric score, where a higher value is bad. So the objective is to show the two worst scoring SubTypes by Station. Sorry I wasn't clear, I have edited the post accordingly to add that context. — aaronlee, Nov 24 '14 at 15:02
Does your table design allow duplicate *Value* values for the same *StationId*? For example, would it permit another row with *StationId STA001* and *Value* 4.63? — HansUp, Nov 24 '14 at 15:24
Yes, it is possible. However, it would be for a different SubTypeId as the table has a composite PK. — aaronlee, Nov 24 '14 at 15:45
What is the problem that arises with your most recent attempt? — Gordon Linoff, Nov 24 '14 at 15:59
updated original post. I have the ordering correct, however I need to account for multiple equal values as was foreshadowed in the above comment. — aaronlee, Nov 24 '14 at 16:14

score 0 · Answer 1 · answered Nov 25 '14 at 13:39

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.

MS-Access SELECT TOP N subquery SQL

1 Answers1