Complex MySQL rank query with ties

Let's say I had the following tab开发者_开发知识库le:

id | name | points  
-------------------
1  | joe  | 100  
2  | bob  | 95  
3  | max  | 95  
4  | leo  | 90

Can I produce a reversed rank recordset like so:

id | name | points | rank  
--------------------------
4  | leo  | 90     | 1  
3  | max  | 95     | 2.5  
2  | bob  | 95     | 2.5   
1  | joe  | 100    | 4  

This is a fully working example, with this sample table

create table tpoints (id int, name varchar(10), points int);
insert tpoints values
(1  ,'joe', 100 ),
(2  ,'bob', 95  ),
(3  ,'max', 95  ),
(4  ,'leo', 90  );

The MySQL query

select t.*, sq.`rank`
from
(
    select
       points,
        @rank := case when @g = points then @rank else @rn + (c-1)/2.0 end `rank`,
       @g := points,
       @rn := @rn + c
    from 
       (select @g:=null, @rn:=1) g,
        (select points, count(*) c
        from tpoints
        group by points
        order by points asc) p
) sq inner join tpoints t on t.points = sq.points
order by t.points asc;

It also has the benefit of performing very well compared to performing a correlated cross (self) join.

• 1x pass through tpoints to aggregate into groups
• calculation of rank with ties
• 1x join to table to put ranks against the records.

Won't do "2.5" as a rank value, but duplicates will have the same number if you use:

  SELECT x.id, 
         x.name,
         x.points,
         (SELECT COUNT(*)
            FROM YOUR_TABLE y
           WHERE y.points <= x.points) AS rank
    FROM YOUR_TABLE x
ORDER BY x.points