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
MySQL doesn’t have analytic function support (ROW_NUMBER, RANK, DENSE_RANK), which is generally what you’d use for requirements like these.
SELECT @rownum := @rownum + 1 AS ranking FROM YOUR_TABLE t JOIN (SELECT @rownum := 0) r ORDER BY t.value
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.
set @i = 0; select myValue, @i:[email protected]+1 as Ranking from myTable order by myValue ASC