CODEWITHSUNDEEP
mysql
Ice
Ice

Reputation: 169

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

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

For example, only 111 people have spent more money than User 12392, so they would be rank 112.

How could I query this?

Upvotes: 7

Views: 3099

Answers (3)

Chris J
Chris J

Reputation: 1375

How about:

SELECT count(*) FROM users WHERE money_sent < (
    SELECT money_sent FROM users WHERE user = 'joe'
);

Upvotes: 13

Bill Karwin
Bill Karwin

Reputation: 562368

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;

Upvotes: 2

Learning
Learning

Reputation: 8185

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

Upvotes: 2

Related Questions