MySQL: Getting a row number (ranking) for a specific row

Question

I have a table for users, there's a column called money_sent. I want to order this table by money sent (most->least so DESC) and then find out which row number (ranking) a specific user is.

So that I can output, User 12392 is 112/50000 people when it comes down to money_spent.

Any ideas for query for this?

Answer 1

How about:

select count(*) from users where money_sent < ( select money_sent from users where user = 'joe' );

Answer 2

SELECT Row,user, money_sent
FROM (SELECT @row := @row + 1 AS Row, user, money_sent 
       FROM table1 order by money_sent desc) 
As derived1

Answer 3

If you also want to get the user's row along with that user's rank, you can use something like this:

SELECT u1.*, COUNT(u2.user) 
FROM users u1
  LEFT OUTER JOIN users as u2 ON (u1.money_sent < u2.money_sent)
GROUP BY u1.user;