CODEWITHSUNDEEP
phpmysqlsql
user961882
user961882

Reputation: 237

PHP MySQL get position of record in ordered recordset

I'm looking at how I can get the position of a record in an ordered SQL recordset

It's an online leaderboard, I've set the id of the last submitted score to $myID, so now I need to get what ranking it got.

I'll be ordering them like so;

SELECT * FROM leaderboards ORDER BY score DESC

What do I add to the statement to just get a specific position of an id

Upvotes: 0

Views: 242

Answers (2)

kwarunek
kwarunek

Reputation: 12587

Most common approach is:

SET @rowcount = 0;
SELECT lb.*, (@rowcount := @rowcount + 1) as 'position' 
  FROM leaderboards lb ORDER BY score DESC

and whole query would look like

SET @rowcount = 0;
SELECT rc.position 
  FROM (
    SELECT lb.id, (@rowcount := @rowcount + 1) as 'position' 
      FROM leaderboards lb ORDER BY score DESC
  ) rc
  WHERE rc.id = $myID

Upvotes: 2

Lorenz Meyer
Lorenz Meyer

Reputation: 19915

This would give the desired result

SELECT count(id) FROM leaderboards where score >= (
    select score from leaderbords where id = $myID
)

Upvotes: 0

Related Questions