Hello,

Need your help with the following. Not sure exactly whether this can be done with sqls alone...

Let's say I've data in the table as follows:


When sorted (ordered by points DESC, then username), the results would appear as:

I need a query to select a user and return his username and ranking in the ordered data. For example:

query mary
return mary 2

query sean
return sean 5

Is there a way to get to his ranking?

Currently, I've an sql to do the sorting of the data. I then use a loop with a counter to find out his ranking.

Could I do it with sqls alone?

Thanks in anticipation

Thanks, guelphdad

I'm using MySQL version 4.1.15...I got some errors when I tried your syntax above. Is it because it's not supported?

you got some errors? hmm, let me guess what they might be...

... Elephants in the butter dish!

Just a quick question :
Does anyone else think that doing a COUNT for each row you a reading is going to be very inefficient when the row numbers start to get very large? I'm trying to understand the efficiency path which the MySQL optimiser might choose with respect to this query.

depends on the indexes, doesn't it

Very true, in that case would it be an appropriate answer to say that if you index the "points" column the count will return a result rapidly? The other option I was considering would have been to use a VAR in your SQL which you increment per row. However this doesn't help for those who have the same points score. What do you think about this?

me? i think you should test it and show us the results

LOL, that would be great if i had the time. Give me ten minutes...

Hi guelphdad,

Sorry, wasn't careful in my first attempts.

It works beautifully. Many thanks I can now get the sorted records with the rankings added.

But how do I get the result for a particular user?

query john
return john, 4

Thanks in advance

add a WHERE clause:

Hi r937,

Thanks! That works

But when the sorted set is as follows:

aa 9
bb 6
cc 6
dd 6
ee 4

A query for bb, cc, dd gets the ranking of 2, instead of 2 for bb, 3 for cc and 4 for dd.

Am I missing something?

yup, i believe you are missing the concept of ties

6 equals 6 equals 6, so i don't see how you can possibly think that cc is third

Ah I see. But it correctly returns ee with a ranking of 5 (not 3):

aa 9 (1)
bb 6 (2)
cc 6 (2 or 3)
dd 6 (2 or 4)
ee 4 (5)

So I was wondering they must be a 3 and 4 ranking instead of three 2's.