Dealing with Ties in a Rank Query in Access

[Tiddles]

Hi

I have a query that ranks some scores, which you kind folks helped me with. However the query isn't quite ranking tied scores as I would like. Instead of 1,2,2,4 it provides 1,3,3,4.

I have been looking at various posts and a help page at http://support.microsoft.com/kb/120608 but I can't get my syntax right to perform the query.

I am getting a little desparate as I need the db at a competition tomorrow!

Many many thanks


Here is my query:

SELECT   Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score, COUNT(*) as Rank
FROM     (
         SELECT Class_ID, Dog_Handler_ID, Club, Score
         FROM Q_Results
         GROUP BY Class_ID, Dog_Handler_ID, Club, Score) AS Res1
         INNER JOIN
         (
         SELECT Class_ID, Dog_Handler_ID, Club, Score
         FROM Q_Results
         GROUP BY Class_ID, Dog_Handler_ID, Club, Score
         ) AS b
  ON     Res1.Class_ID = b.Class_ID
 AND     Res1.Score >= b.Score
GROUP BY Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score
ORDER BY Res1.Class_ID, Res1.Score, COUNT(*)

[NeoPa]

Ranking is normally done by counting (or summing if you prefer) the number of items in the list which are better, using your own definition of better of course.

In this case I suspect changing >= into = on line #13 would probably sort you out. You will need to add 1 to the value received though. This would return 0, 1, 1, 2 otherwise.

[Tiddles]

Hmm - that didn't work - it changed everything the ranking altogether and I only got ranks 1 and 2 rather than 1 through 17 that I was getting before.

Thank you though!

[NeoPa]

How about :

SELECT   Class_ID,
         Dog_Handler_ID,
         Club,
         Score,
         (SELECT COUNT(*)
          FROM   Q_Results
          WHERE  Score>QR.Score)+1 AS Rank
 
FROM     Q_Results AS QR
 
ORDER BY Class_ID,
         Score