Row number

[adam smith]

Hey guys,
I am a developer of an online game in ASP, in which the users have a certain amount of XP. What I want is to sort the players by XP and retrieve the rank for a certain user id without using any ASP coding.

I have this query which does the job:
SET @row =0;
SELECT rk FROM (SELECT @row:=@row+1 rk, uRank.* FROM
(SELECT uID, SUM(tblbase.basXP) AS uXP
FROM tbluser INNER JOIN tblbase ON tbluser.uID = tblbase.basUserID
GROUP BY tblbase.basUserID
ORDER BY uXP DESC) AS uRank) AS query WHERE query.uID = 5

This works fine. However, this does not take into account that multiple players might have the same amount of XP, and hence should have the same rank.
I was thinking of a GROUP BY uRank.uXP but that wouldn't work as user id's would disappear too. Is there another way in which I can achieve this?

Thank you,
Adam



Edited 2 time(s). Last edit at 03/02/2009 06:22PM by adam smith.