By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,544 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Dealing with Ties in a Rank Query in Access

P: 8
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT   Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score, COUNT(*) as Rank
  2. FROM     (
  3.          SELECT Class_ID, Dog_Handler_ID, Club, Score
  4.          FROM Q_Results
  5.          GROUP BY Class_ID, Dog_Handler_ID, Club, Score) AS Res1
  6.          INNER JOIN
  7.          (
  8.          SELECT Class_ID, Dog_Handler_ID, Club, Score
  9.          FROM Q_Results
  10.          GROUP BY Class_ID, Dog_Handler_ID, Club, Score
  11.          ) AS b
  12.   ON     Res1.Class_ID = b.Class_ID
  13.  AND     Res1.Score >= b.Score
  14. GROUP BY Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score
  15. ORDER BY Res1.Class_ID, Res1.Score, COUNT(*)
Mar 19 '10 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 19 '10 #2

P: 8
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!
Mar 19 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
How about :
Expand|Select|Wrap|Line Numbers
  1. SELECT   Class_ID,
  2.          Dog_Handler_ID,
  3.          Club,
  4.          Score,
  5.          (SELECT COUNT(*)
  6.           FROM   Q_Results
  7.           WHERE  Score>QR.Score)+1 AS Rank
  8.  
  9. FROM     Q_Results AS QR
  10.  
  11. ORDER BY Class_ID,
  12.          Score
Mar 20 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.