SELECT TOP query - Only returning 3 values when duplicates are allowed

[SALAlly]

Hey,

I've been developing my first MS Access database for a while now and making some steady progress. I've got various tables and queries set up which are all working well, but am stuck on one particular issue.

This is an athletics scoring database, which converts an athletes individual performance into scores.

I'm using SELECT TOP 3 to return the best three scores from a list of performances by any given athlete.
Sometimes an athlete will score the same points for repeated performances - imagine your best 5 results score 700, 700, 650, 650 and 400 points. I therefore only want to total 700+700+650=2050points.

My code is

SELECT t.AthleteID, t.AthleteName, t.Gender, t.AgeGroup, t.ClubID, t.ClubCategory, t.EventID, t.UniquePerfID, t.Score
FROM [T05 All Throws Performances] AS t
WHERE (((t.Score) In (SELECT TOP 3 Score
FROM [T05 All Throws Performances]
WHERE AthleteID = t.AthleteID
ORDER BY Score DESC
)))
ORDER BY t.AthleteID, t.Score DESC;


[\code]

What this is doing is returning 700+700+650+650=2700, how do I drop the lowest duplicate score and only total 3?

 

[isladogs]

Welcome both to AWF and MS Access Forums where you have also posted this question
http://www.accessforums.net/showthread.php?t=71490

For future info, there's nothing wrong with cross posting on more than one forum BUT you should always state you have cross posted and provide the link yourself

Anyway, I think you will need to make the subquery into a ranking query.
Suggest you read this thread where I gave several examples using a Serialize function
https://www.access-programmers.co.uk/forums/showthread.php?t=297922

 

[arnelgp]

I think you need to sum it in a sub query:

SELECT t.AthleteID, t.AthleteName, t.Gender, t.AgeGroup, t.ClubID, t.ClubCategory, t.EventID, t.UniquePerfID, (SELECT SUM(SCORE) FROM (SELECT TOP 3 SCORE FROM [TO5 ALL THROWS PERFORMANCES] AS T1 WHERE T1.ATHLETHID=T.ATHLETHID)) AS SUMSCORE
FROM [T05 All Throws Performances] AS t

 

[Pat Hartman]

You can use a query that groups the results of the top 3 and then a final query to sum the group query.

 

[SALAlly]

Thanks everyone,
Gave me some ideas of what to search and found a solution by searching to break ties.

Gave me the answer,

Code now looks like this -

SELECT t.AthleteID, t.AthleteName, t.Gender, t.AgeGroup, t.ClubID, t.ClubCategory, t.EventID, t.UniquePerfID, t.Score
FROM [T05 All Throws Performances] AS t
WHERE (((t.UniquePerfID) In (SELECT TOP 3 UniquePerfID
FROM [T05 All Throws Performances]
WHERE AthleteID = t.AthleteID
ORDER BY Score DESC, UniquePerfID
)))
ORDER BY t.AthleteID, t.Score DESC, t.UniquePerfID;

 

[isladogs]

Excellent. Please mark as SOLVED on both forums!