MySQL Forums
Forum List  »  General

Row number
Posted by: adam smith
Date: March 02, 2009 06:19PM

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,

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

Options: ReplyQuote

Written By
Row number
March 02, 2009 06:19PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.