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

Question

I have a users table that has a column called money_sent . I want to order this table in money_sent in descending order, and then find out what "rank" a particular user has.

For example, only 111 people spent more money than user 12392, so they will be rank 112.

How can I request this?

Answer 1

What 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 string along with this user 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;