CODEWITHSUNDEEP
phpmysql
Tim
Tim

Reputation: 14446

Finding a ranking from a rating field on MySQL

I have a MySQL table that looks like this:

id (int primary)
name  (text)
rating (float)

I have a page showing rankings which looks like this:

$i = 0;
$q = mysql_query("SELECT * FROM teams ORDER BY rating DESC");
while($r = mysql_fetch_assoc($q)){
    $i++;
    print("$i: {$r['name']}<br>");
}

This shows teams in order of their rating, with a ranking. And it works.

Now, if I'm given the ID of a team, how do I find their ranking without running through the loop like this? A single MySQL query which returns the team's info + a numeric ranking indicating how far down the list they would be, if I had rendered the whole list.

Thanks!

Upvotes: 0

Views: 131

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

You can also do it this way:

select * from (
  select t.*, @rank := @rank + 1 as rank
  from (select @rank := 0) as r, t
  order by rating desc
) as t
where id = 20

Upvotes: 0

mathematical.coffee
mathematical.coffee

Reputation: 56905

To get the ranking you can do:

SELECT COUNT(*) as ranking
FROM teams t
WHERE t.rating >= (SELECT rating FROM teams WHERE id=$ID);

To get all the relevant info too, you can do:

SELECT t.*,COUNT(*) as rank
FROM teams t
JOIN teams t2 ON t.rating<=t2.rating
WHERE t.id=4;

This joins teams to itself joining on t.rating <= t2.rating, and so you get one row for every team that has a rating higher than or equal you.

The COUNT just counts how many teams have a rating higher than or equal to you.

Note that if there's a tie this will give you the lower rank. You can change the <= to a < if you want the highest.

Upvotes: 1

Related Questions