Sum top 3 query

[Guest]

Hi

I am arranging sporting events for a group of people,
there are 9 events throughout the year. some people
attend all events, some people attend some, and some none.

each person attending receives rankingpoints according to result in
that particular event.

at the end of the year we select a winner counting the 6 best results
throughout the year.
so if you attended all nine events you can only count your best 6 results
if you attended only three events, you would use your result from those
three and
obviously 0 from the remaining 3 events.

the table looks like this
ID event competitorID rankingpoints
1 1 1 1
2 1 2 4
3 2 3 2
(counter)
and so on

I'm trying to create a query where you know a competitorID (1 for instance)
and receive the sum of that persons 6 best results

I have tried:
SELECT TOP 6 tblChallenge.CompetitorId, Sum(tblChallenge.Ranking) AS
sumforRanking
FROM tblChallenge
GROUP BY tblChallenge.CompetitorId
HAVING (((tblChallenge.CompetitorId)=1))
ORDER BY Sum(tblChallenge.Ranking);

But that gives me the total sum for all events attended and not just the top
6 that i want.

Also, I managed to get it to display the top 6 results on 6 different rows
which is close, but I want just one row, where the top 6 results are added

so the output i would like is (for instance)
CompetitorID SumForRanking
1 32

I am new to this as you probably can tell, so any help is appreciated

Thanks

 

[Michel Walsh]

Hi,


I will do it in two queries:



q1:


SELECT a.competitorID, LAST(a.rankingPoints) As ranking, a.id
FROM myTable As a INNER JOIN myTable As b
ON a.competitorID = b.competitorID
AND (a.rankingPoints> b.rankingPoints
OR (a.rankingPoints = b.rankingPoints AND a.id >= b.id)
GROUP BY a.competitorID, a.id
HAVING COUNT(*) <=6




q2:

SELECT competitorID, SUM(ranking)
FROM q1
GROUP BY competitorID




Hoping it may help,
Vanderghast, Access MVP