CODEWITHSUNDEEP
mysqlsql
Arturs Vancans
Arturs Vancans

Reputation: 4640

How to determine the place of a row in ordered SQL table?

I have a table of users and a 'points' column. I would like to determine the number/place of the row across all the users ordered by 'points'.

I could just get result of all users data and then do while loop, and stop when id equals necessary user. But I believe there is a more efficient way do to that because my table will contain ~100 000 rows.

Upvotes: 1

Views: 41

Answers (2)

Marc
Marc

Reputation: 16512

You need the row number of your record

Here's a good way to do it in MySQL

It would look like this

SELECT rank_user.* 
FROM 
(
   SELECT @rownum:=@rownum+1 ‘rank’, p.* 
   FROM user u, (SELECT @rownum:=0) r   
   ORDER BY points DESC
) rank_user 
WHERE rank BETWEEN 2 AND 4;

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

SET @rownum = 0; 
Select sub.*, sub.rank as Rank
FROM
(
   Select *,  (@rownum:=@rownum+1) as rank
   FROM   users
   ORDER BY points 
) sub
WHERE rank = 15

Upvotes: 1

Related Questions