Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Ranking with allowance for tied scores 1

Status
Not open for further replies.

TrekBiker

Technical User
Nov 26, 2010
330
GB
I need to extract 1st, 2nd and 3rd scores from a table, allowing for tied scores.

An abbreviated example of the table (it has many more rows for each date)is

Best_Scores_tev12q.jpg


The results for each date need to be like this, limiting to the top placings, including any ties

Winners_tidlnn.jpg


I've found various posts about ranking but not quite what I want.
 
This was the query I tried

Code:
SELECT Dupe.CompDate, Dupe.Player, Dupe.Points, Dupe.Points
FROM tblScores AS Dupe
WHERE (((Dupe.Points) In (SELECT TOP 3 tblScores.Points FROM tblScores WHERE (((tblScores.Player)=Dupe.[Player])) ORDER BY  tblScores.Points DESC)))
ORDER BY Dupe.CompDate, Dupe.Points DESC;

It sometimes gave the right result, sometimes going beyond the top 3, particularly when there were tied points.

Rank_jxupcp.jpg
 
You need top 3 for given date, so in [tt]WHERE[/tt] condition you need [tt]tblScores.CompDate=Dupe.CompDate[/tt] instead of linking by [tt]Player[/tt].

combo
 
Ah, great! Thanks Combo, moment of madness now rectified and working properly.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top