sql output as a ranking

Question

If I have a table of random values in a sql table, how can I export and display them as a raking rather than the absolute value... for example.. if the three values are 30 85 and 90 how do i get 30 do display as 1 or 1st, 85 as 2 or 2nd etc

possible duplicate of [What is the best way to generate ranks in MYSQL?](http://stackoverflow.com/questions/431053/what-is-the-best-way-to-generate-ranks-in-mysql) — JohnFx, Oct 20 '10 at 18:15

score 2 · Accepted Answer · answered Oct 20 '10 at 18:16

MySQL doesn't have analytic function support (ROW_NUMBER, RANK, DENSE_RANK), which is generally what you'd use for requirements like these.

Use:

  SELECT @rownum := @rownum + 1 AS ranking
    FROM YOUR_TABLE t
    JOIN (SELECT @rownum := 0) r
ORDER BY t.value

Example:

  SELECT x.num,
         @rownum := @rownum + 1 AS ranking
    FROM (SELECT 30 AS num
          UNION ALL
          SELECT 85
          UNION ALL
          SELECT 90) x
    JOIN (SELECT @rownum := 0) r
ORDER BY x.num

You could use:

SELECT (SELECT COUNT(*)
          FROM YOUR_TABLE b
         WHERE b.value <= a.value) AS ranking
  FROM YOUR_TABLE a

...but duplicates would have the same ranking value. And you have to make sure the value comparison is in the correct direction.

score 1 · Answer 2 · edited Oct 20 '10 at 18:15

1

Try:

set @i = 0;
select myValue,
        @i:=@i+1 as Ranking
from myTable 
order by myValue ASC

edited Oct 20 '10 at 18:15

JohnFx

34,209
18
103
160

answered Oct 20 '10 at 18:13

p.campbell

95,348
63
249
319

You probably want an `ORDER BY myValue ASC` in there. – Byron Whitlock Oct 20 '10 at 18:15

sql output as a ranking

2 Answers2