Ranking Records

Another common request is to rank records, or a subset of records, against each other. Think of sports box scores, top ten lists or consumer product rankings. Take this example table:

tblScore:
  CompID   - Long (PK)
  PlayerID - Long
  Score    - Long

CompID    PlayerID  Score
1         1         39
1         2         38
1         3         32
2         1         41
2         2         39
2         3         41
3         1         42
3         2         41
3         3         43
4         1         39
4         2         33
4         3         42
5         1         44
5         2         42
5         3         28
6         1         39
6         2         42
6         3         47
            

Typically some subset of records needs to be defined (e.g. CompID = 6) and then a sequential number needs to be generated to indicate ranking. A sub-query is used to count the number of records that meet or exceed the current record’s Score value and this becomes the ranking. This sub-query becomes a correlated sub-query because of the need to only count scores for our subset of records and and to count records that meet criteria found in the outer query:

SELECT 
  tblScores.PlayerID
, tblScores.Score
, (SELECT Count(*) FROM tblScores As S WHERE S.CompID = 6 AND S.Score >= tblScores.Score) AS Rank
FROM tblScores
WHERE (((tblScores.CompID)=6))
ORDER BY tblScores.Score DESC;            
            

And the results look like this:

PlayerID  Score     Rank
3         47        1
2         42        2
1         39        3
            

Additionally it may be desirable to calculate a “behind” value. This query generates that value:

SELECT 
  tblScores.PlayerID
, tblScores.Score
, IIf( ((SELECT Max(Score) FROM tblScores WHERE CompID = 6) - tblScores.Score) = 0, "----"
  , ((SELECT Max(Score) FROM tblScores WHERE CompID = 6) - tblScores.Score)) AS Behind
, (SELECT Count(*) FROM tblScores As S WHERE S.CompID = 6 AND S.Score >= tblScores.Score) AS Rank
FROM tblScores
WHERE (((tblScores.CompID)=6))
ORDER BY tblScores.Score DESC;            
            

and the results look like this:

PlayerID  Score     Behind    Rank
3         47        ----      1
2         42        5         2
1         39        8         3          
            

See a more advanced version of ranking – only within a group further down the page.