Mysql - How to get a row number after Order by?

Question

Let's say I have a table with the following columns:

p_id

userid

points

Let's say these columns have over 5000 records. So we actually have users with points. Each user has an unique row for their point record. Imagine that every user can get points on the website by clicking somewhere. When they click I update the database with the points they get.

So we have a table with over 5000 records of people who have points, right? Now I would like to order them by their points (descending), so the user with the most point will be at the top of the page if I run a MySQL query.

I could do that by simply running a query like this:

SELECT `p_id` FROM `point_table` ORDER BY `points` DESC

This query would give me all the records in a descending order by points.

Okay, here my problem comes, now (when it is ordered) I would like to display each user which place are they actually. So I'd like to give each user something like this: "You are 623 of 5374 users". The problem is that I cannot specify that "623" number.

I would like to run a query which is order the table by points it should "search" or count the row number, where their records are and than return that value to me.

Can anyone help me how to build a query for this? It would be a really big help. Thank you.

The answer below **does not work** because the `order by` will be evaluated **after** the variable has been selected. See the caveat at http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select/29846430#comment47818852_2520392 — Pacerier, Apr 24 '15 at 11:54

score 16 · Accepted Answer · edited May 23 '17 at 11:53

16

This answer should work for you:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, p_id FROM point_table ORDER BY points DESC;

Update: You might also want to consider to calculate the rank when updating the points and saving it to an additional column in the same table. That way you can also select a single user and know his rank. It depends on your use cases what makes more sense and performs better.

Update: The final solution we worked out in the comments looked like this:

SELECT
rank, p_id
FROM
    (SELECT
     @rank:=@rank+1 AS rank, p_id, userid
     FROM
     point_table, (SELECT @rank := 0) r
     ORDER BY points DESC
    ) t
WHERE userid = intval($sessionuserid);

edited May 23 '17 at 11:53

Community

1
1

answered Sep 09 '12 at 17:56

sprain

7,552
6
35
49

1

It is actually not working for me, because it gives me always "1" when the correct number should be 2 in my case. you have also forgot to select rows ONLY for the specific user. – user1406071 Sep 09 '12 at 18:01
Can you show us your updated code and maybe even a few sample entries? – sprain Sep 09 '12 at 18:02
1

mysql_query("SET @rank=0;"); return mysql_query("SELECT @rank:=@rank+1 AS rank, p_id FROM point_table WHERE userid = $sessionuserid ORDER BY points DESC"); – user1406071 Sep 09 '12 at 18:12
You query is only returning one entry because your looking for a specific user. This is something you didn't do in the example in your inital question. So either recalculate the rank regularly and save it to the database or query for all users and then find the one you're looking for in the result. The first way will probably be better. Consider giving your ranking a certain time lag, you probably don't really have the need to update it every second as you mentioned in a different comment. – sprain Sep 09 '12 at 18:28
1

So there is no such query like I ORDER the data in the table, than give them a row number, and pick THAT SPECIFIC row number, where the userid = userid??? There is no thing like that? :OO That would be very intersting. – user1406071 Sep 09 '12 at 18:35
Actually, there is. However it is actually running two queries in one which might slow down your performance. But give it a try! `SELECT rank, p_id FROM (SELECT @rank:=@rank+1 AS rank, p_id FROM point_table, (SELECT @rank := 0) r ORDER BY points DESC) t WHERE userid = intval($sessionuserid);` – sprain Sep 09 '12 at 18:44
2

Thank you, but its actually not working :/ By the way what is that "r" and "t" there? – user1406071 Sep 09 '12 at 19:06
Define "not working" - what do you get? r and t are defining aliases for the tables which we are creating on the fly. – sprain Sep 09 '12 at 19:07
It says: Unknown column 'userid' in 'where clause' But I can assure you I have that userid in my table. – user1406071 Sep 09 '12 at 19:17
My bad, try again: `SELECT rank, p_id FROM (SELECT @rank:=@rank+1 AS rank, p_id, userid FROM point_table, (SELECT @rank := 0) r ORDER BY points DESC) t WHERE userid = intval($sessionuserid);` – sprain Sep 09 '12 at 19:19
ITS WORKING MAN! :) Thank you bro I love you..lol Do you think it requires more resource than the databe updating thing? – user1406071 Sep 09 '12 at 19:31
Depends a lot on how often you are calling those queries. As long as thinks work out, go this way. – sprain Sep 09 '12 at 19:52

score 6 · Answer 2 · answered Sep 18 '19 at 17:48

Row number after order by

SELECT ( @rank:=@rank + 1) AS rank, m.* from                                   
(                            
  SELECT a.p_id, a.userid                                                   
  FROM (SELECT @rank := 0) r, point_table a 
  ORDER BY a.points DESC                                                     
) m

score 0 · Answer 3 · answered Feb 21 '21 at 07:31

For some reason the accepted answer doesn't work for me properly - it completely ignores "ORDER BY" statement, sorting by id (primary key)

What I did instead is:

SET @rn=0;
CREATE TEMPORARY TABLE tmp SELECT * FROM point_table ORDER BY points DESC;
SELECT @rn:=@rn+1 AS rank, tmp.* FROM tmp;

score -1 · Answer 4 · answered Sep 09 '12 at 18:02

-1

Add a new column for position to the table. Run a cron job regularly which gets all the table rows ordered by points and then update the table with the positions in a while loop.

answered Sep 09 '12 at 18:02

yunzen

32,854
11
73
106

I do not actually want to send an update to the table that much. That is because if I'll have 100,000 members and 20,000 of them is updating the databse in every 1 sec, that is a disaster. Thats why I just want to get the rank of them be using a query. I'll don't do anyithing else with it, just need the rank. – user1406071 Sep 09 '12 at 18:09
I think that's what stackoverflow does. They do not do this every second. They do it once a day. And browser game rankings do it the same. – yunzen Sep 09 '12 at 19:08
Hmm, and do you think it is better / fater than running 2 query in 1? – user1406071 Sep 09 '12 at 19:20
It should be the same. The difference is. Here you do it once and there you do it all the time – yunzen Sep 09 '12 at 19:32

Mysql - How to get a row number after Order by?

4 Answers4

Linked