mySQL Ranks Ties To Next Higher Number -

rank ties works correctly except first record missing, no matter how it's fetched. trying rank low high.

select x.*,  count(*) myrank  mytablename x  join mytablename y  on x.number > y.number  group id   order myrank  

pleased say, after long battle, the following revision works intended!

select x.*,  count(y.id)+1 myrank mytablename x  left join mytablename y  on (x.number > y.number)  group x.id  order myrank 

result:

id  #   rank  -50  1 b  -40  2 c  -40  2 d  -30  4 e  -30  4 

adding "=" to... on x.number >= y.number ...gets ranking ties right, except tied records rank next higher number.

id  #   rank  -50  1 b  -40  3 c  -40  3 d  -30  5 e  -30  5 

ranking high-to-low on x.number <= y.number same thing.

here's version without self-join rather using variables. it's faster:

select id, points, rank ( select t.*, @rownum := @rownum + 1, @rank := if(@prev_points = points, @rank, @rownum) rank, @prev_points := points test t, (select @rank:=0, @rownum:=0, @prev_points) var_init order points desc )sq 

• see working live in sqlfiddle