Ranking

[ACTHelp]

I need to write a query to rank a group of individuals based upon a score within a month.

My data looks like this:

Rep Month Year Score
Dana 1 2004 33
Dana 2 2004 47
Dana 3 2004 65
Dana 4 2004 23
Dana 5 2004 56
Dana 6 2004 67
John 1 2004 45
John 2 2004 76
John 3 2004 23
John 4 2004 56
John 5 2004 67
John 6 2004 78
Andy 1 2004 56
Andy 2 2004 34
Andy 3 2004 67
Andy 4 2004 34
Andy 5 2004 78
Andy 6 2004 89


So I need to rank the individuals within the year and month based upon their score with the highest score being #1.

Output should look like this:

Rep Month Year Score Rank
Andy 1 2004 56 1
John 1 2004 45 2
Dana 1 2004 33 3
John 2 2004 76 1
Dana 2 2004 47 2
Andy 2 2004 34 3
Andy 3 2004 67 1
Dana 3 2004 65 2
John 3 2004 23 3
John 4 2004 56 1
Andy 4 2004 34 2
Dana 4 2004 23 3
Andy 5 2004 78 1
John 5 2004 67 2
Dana 5 2004 56 3
Andy 6 2004 89 1
John 6 2004 78 2
Dana 6 2004 67 3



Help!

 

[PHV]

SELECT A.Rep, A.Month, A.Year, A.Score, Count(*) AS Rank
FROM tblScores AS A INNER JOIN tblScores AS B
ON A.Year=B.Year AND A.Month=B.Month AND A.Score<=B.Score
GROUP BY A.Month, A.Year, A.Score, A.Rep
ORDER BY 3, 2, 5;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244