CODEWITHSUNDEEP
phpmysqlsql-order-byranking
wnajar
wnajar

Reputation: 757

Order by descending and returning the record number to use for ranking

I'm trying to rank users very simply by their reputation, which is stored as an int column in a MySQL table of users. In order to find out which users have the highest reputation from highest to lowest, I would do something like:

SELECT * FROM users ORDER BY reputation DESC

Which would give me users ordered by highest reputation to lowest. Say user Tim shows up as the 3rd record. How do I return which # record he is in that order, i.e his rank? This seems really simple but I'm drawing a blank on how to do it. Essentially I want the record # after ordering to say "he is the user with the third highest reputation". Alternatively, is this the wrong way to go about ranking?

I can specify a user in users by the column user_id and just mainly want to return the user's rank in one query, if possible.

I'm using Laravel as a framework if anyone knows a shortcut using that, but just generally with SQL would be fine too.

Upvotes: 0

Views: 146

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26343

This is similar to the Sam D answer, but it puts everything into a single query:

SELECT
  @rank := @rank + 1 AS RepRank,
  users.*
FROM users, (SELECT @rank := 0) rnk
ORDER BY reputation DESC;

To limit results to a specific user you'll need to do an outer query - there's really no way around it. Here's how (note that the inner query is the same as the query shown above):

SELECT * FROM (
  SELECT
    @rank := @rank + 1 AS RepRank,
    users.*
  FROM users, (SELECT @rank := 0) rnk
  ORDER BY reputation DESC
) RankedUsers
WHERE User_ID = 5

Upvotes: 2

Sam
Sam

Reputation: 2761

Are you looking for something like this?

set @rank:= 0;

SELECT (@rank:= @rank+ 1) AS Rank , users.* FROM users ORDER BY reputation DESC;

This will increment @rank for each resulting record. For a specific user use:

SELECT T.reprank 
FROM   (SELECT @rank := @rank + 1 AS reprank, 
               users.* 
        FROM   users, 
               (SELECT @rank := 0) rnk 
        ORDER  BY reputation DESC) AS T 
WHERE  id = 23

Upvotes: 2

Related Questions