Sum of Top Scores

[KashLeeC]

Hi there again!

I have a table called Single Score. It has five fields:

School, Classification, Full Name, Score, Event

School = name of school
Classification = Size of school
Full Name = Contestants name
Score = Score on test
Event = event the test was taken in

Here is my conundrum. I need a query that treats each school like a team, taking the top three individual scores from each and adding them together.

It should pull into the query with these four fields:

School, Classification, Score, Event

Any suggestions?

Thanks in advance!

[hansup]

With these scores for one School/Event combination, how will you determine which are the top 3?

Score
100
99
98
98
98
85

[KashLeeC]

With these scores for one School/Event combination, how will you determine which are the top 3?

Score
100
99
98
98
98
85


It doesn't really matter who the person is, I just need the top three scores. So 100, 99, and whichever 98 it takes.

[hansup]

My first thought was this could be handled with a correlated subquery which does something like this for each combination of School and Event.

SELECT TOP 3 Score
FROM [Single Score]
WHERE
School = "something"
AND Event = "something else"
ORDER BY Score;

A problem with that approach is that TOP 3 will return ties, so could actually give you more than 3. If [Full Name] is unique, you could use it as a tie breaker.

SELECT TOP 3 Score
FROM [Single Score]
WHERE
School = "something"
AND Event = "something else"
ORDER BY Score, [Full Name];

If [Full Name] is not unique, does your table or a related table contain a unique field (maybe a primary key like student_id) which could be used as a tie breaker?

Once you clear that hurdle, you would still have to build a moderately complex query which uses that example adapted as a subquery. Are you experienced with creating subqueries?

If not, Allen Browne has a page of background information, "Subquery basics": http://allenbrowne.com/subquery-01.html