How to get row position?

Question

Possible Duplicate:
MySQL get row position in ORDER BY
Calculating rank in PHP/MySQL

I have a game system wrote in php, each user has X amount of money. I have a page that display the most rich users in the site.

$ranking = mysql_query("
    SELECT * 
    FROM users 
    ORDER BY money DESC 
    LIMIT 10
");

Now I want give each user his position in the list. I can't select all the information and start to sort just to get one value, it's waste of resources. How can I select the position of a row (where userid = X)?

(MYSQL)

@RedFilter, Sorry, I don't understand what you mean – Alvarez Oct 30 '12 at 17:57 — Alvarez, Oct 30 '12 at 17:57

Tim Lehner · Answer 1 · 2012-10-30T20:53:57.960

Here is a simple and standard way to do this using a correlated subquery:

SELECT *, (SELECT count(*) + 1 FROM users WHERE money > u.money) as MoneyRank
FROM users AS u
WHERE username = 'X'

Here is a SqlFiddle demo.

This does suffer from performance problems as any "triangular join" (SQL Server article, but still applies) would, however. I would still implement it myself this way initially (it's simple), then index if there's a performance problem, then move on to other pre-calculated/cached options if that's not good enough. The comments also mention other solutions that would work.

score 0 · Answer 2 · edited Oct 31 '12 at 02:16

0

Rank column would be the position in the list:

SELECT t.*, 
   @rownum := @rownum + 1 AS rank
FROM users, (SELECT @rownum := 0) r
ORDER BY money DESC 
LIMIT 10

edited Oct 31 '12 at 02:16

air4x

5,618
1
23
36

answered Oct 30 '12 at 17:58

Adriano Carneiro

57,693
12
90
123

I've tried to edit and add the WHERE clause that I need, anyway, it always gives me 1 and not the correct position. – Alvarez Oct 30 '12 at 18:19

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

You can't do this easily/quickly without doing a full table sort. I'd recommend that you only give each person a new rank periodically, perhaps every hour, and update their position in that way.

For the "leaderboard", you don't run into this issue, because you will always have the offset and limit restrictions. So when you display the top 10 (limited), you know their ranks of 1 - 10.

However, just selecting an individual user, you can't do this without a highly inneficient table sort.

Also, see this other question, which (I think is a near exact duplicate?)

Calculating rank in PHP/MySQL

edited May 23 '17 at 12:27

Community

1
1

answered Oct 30 '12 at 18:02

Layke

51,422
11
85
111

I don't mind to do a full table sort, but I want get the result in the resource, and not sort in php. – Alvarez Oct 30 '12 at 18:21

How to get row position?

3 Answers3