Date: 2005-12-16 11:12 Subject: Solution for Calculating Ranking Security: Public

Getting back to the ranking calculation quiz I posted last week....

I was a bit surprised about all the complicated constructs with counters, stored procedures and even views. The solution actually needs to work on a 4.1 server so I should perhaps have banned using stored procs, but I didn't expect them to be needed - however I understand that people are very excited about MySQL 5 and all its new capabilities ;-)

My own early solution (which I worked out after posting the quiz) was:

SELECT COUNT(DISTINCT score) AS ranking
  FROM points
 WHERE score >= (SELECT score FROM points WHERE id=#)

Which is, provided we index the (score) column as well, very fast (the subselect actually gets optimized away into a constant). The server will only have to use the index. It handles ex aequo positions but without skipping rankings the way it's normally done. Supposing there are two people in 2nd place, you'd want to skip 3rd place so that the person in 4th place stays there. So, my solution was in fact incorrect ;-)

Giuseppe Maxia was the first to come up with this (I've simplified for readability):

SELECT COUNT(*)+1 AS ranking
  FROM points
 WHERE score > (SELECT score FROM points WHERE id=#)

which is of course just as efficient as the above, and by using the count+1 and > instead of the exact count and >= it also solves the ex aequo problem very neatly.

Giuseppe's solution works fine on its own, although he also showed a fancy version using a VIEW and another using a Stored Function. Anyway, I reckon Giuseppe earned the prize (a MySQL 10th anniversary mug + MySQL sticker). Thanks everybody for participating!

I've received lots of feedback that this kind of quiz idea is much liked. So we're setting up a forum (easier to handle than blog comments) and a new corner in the MySQL Developer Zone for this. The first quiz will be posted shortly, and of course I'll blog about it when it's online. Stay tuned!

Post A Comment | 3 Comments | Add to Memories | Tell a Friend | Link