How Best to Get Someone's 'Rank' from a Scores Table with PHP and MySQL Without Looping

How best to get someone's 'rank' from a scores table with php and mysql without looping

SELECT s1.initials, (
  SELECT COUNT(*)
  FROM scores AS s2
  WHERE s2.score > s1.score
)+1 AS rank
FROM scores AS s1

Finding 'rank' of entry in sql database efficiently

select * from (
    select
    p.*,
    @rank:=IF(@prev_score != score, @rank + 1, @rank) as rank,
    @prev_score := score
    from
    people p
    , (select @rank:=1, @prev_score:=null) var_init
    order by score desc
) sq where rank = 5;

• see it working live in an sqlfiddle

This is for MySQL. Your question is tagged as MySQL, but in your sqlfiddle you specified SQL Server.

For SQL Server it would be this:

; with cte as (
select
p.*,
rank() over (order by score desc) as my_rank
from
people p
) select * from cte where my_rank = 5;

UPDATE:

select * from (
    select
    p.*,
    @rank:=IF(@prev_score != score, @rank + 1, @rank) as rank,
    @prev_score := score
    from
    people p
    , (select @rank:=1, @prev_score:=null) var_init
    order by score desc
) sq where name = 'whatever';

How to optimize a score/rank table with different specific scores and ranks?

Well, you can try creating a table with the following configuration:

id | name | score_overall | score_trade | score_quest | overall_rank | trade_rank | quest_rank

If you do that, you can use the following query to populate the table:

SET @overall_rank:=-(SELECT COUNT(id) FROM users);
SET @trade_rank:=@overall_rank;
SET @quest_rank:=@overall_rank;

SELECT *
FROM users u
INNER JOIN (SELECT id,
                   @overall_rank:=@overall_rank+1 AS overall_rank
            FROM users
            ORDER BY score_overall DESC) ovr
ON u.id = ovr.id
INNER JOIN (SELECT id,
                   @trade_rank:=@trade_rank+1 AS trade_rank
            FROM users
            ORDER BY score_trade DESC) tr
ON u.id = tr.id
INNER JOIN (SELECT id,
                   @quest_rank:=@quest_rank+1 AS quest_rank
            FROM users
            ORDER BY score_quest DESC) qr
ON u.id = qr.id
ORDER BY u.id ASC

I've prepared an SQL-fiddle for you.

Although I think performance will weigh in if you start getting a lot of records.

A bit of explanation: the @*_rank things are SQL variables. They get increased with 1 on every new row.

PHP MYSQL - Get Rank (Position) of Single Item Based on Score

Well, if you just want to display the rank table, you can do this:

$sql = mysql_query( "SELECT * FROM `subm_items` ORDER BY `score` DESC");
$i = 0;
while($row = mysql_fetch_assoc($sql)) {
    $i++;
    echo "Rank ".$i.": ID#".$row['item_id']." - Score: ".$row['score']."<br />\n";
}

If you want to compute the ranks in MySQL, you want something like this:

SET @rank=0;
SELECT (@rank:=@rank+1) as `rank`, `item_id`, `score` FROM `subm_items` ORDER BY `score` DESC

But IMO it's easier to just have a counter in PHP.

---

Related Topics