0

I have a database that has a similar structure to:

http://www.sqlfiddle.com/#!2/6c5239

Currently in a .php script to find the rank of the person I run

SELECT * FROM people ORDER BY score DESC;

and loop through the results, increment a "rank" variable each row until I hit the desired name, output the results, and break the loop.

I feel this is a very inefficient way to get a ranking, both for the webserver to have to loop through the data and also for the SQL database to return an entire table.

Is there an easier way to do this, possibly all through a SQL statement that doesn't tax the SQL server too much?

johnnyb
  • 3
  • 2
  • I'm not so sure that your method is a bad one. But you can do this efficiently in SQL using variables to track the ranks. – Strawberry Feb 11 '14 at 09:23
  • If you don't need to do { SELECT * FROM } don't use it. select the columns you need to query that would bring speed to the database. I can't tell if you need to query all columns. – Mubo Feb 11 '14 at 10:43

1 Answers1

0
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;

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';
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • My bad, SQL fiddle should be fixed now. Your code works to find by rank, but how can I find by username and return their ranking? For example, I would find out what rank 'fred' is in relation to all other entries (by score). – johnnyb Feb 11 '14 at 10:40
  • @johnnyb Had to fix an error, it really is rank now, not rownumber :) – fancyPants Feb 11 '14 at 10:45
  • @johnnyb With the latest fix the variable has to be initiated with 1 instead of 0. Hope you noticed this. – fancyPants Feb 11 '14 at 10:54