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.
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