0

A MySQL table looks like this:

Item  Score
-----------
A     3.646
B     2.775
C     6.383
D     0.122
...

There is an index on column Score. I want to select items in the order of descending score, and I also want to know the rank of an item (i.e. the number of that particular row in the result set). In this example, the desired result would be:

Item  Rank
----------
C     1
A     2
B     3
D     4

Is there an efficient way to do this on big tables? I would like to avoid sub-selects if possible.

P.S.: yes, the rank in the result set will of course always be 1, 2, 3, ... but no, I cannot just derive this in the application because these results will be further processed within the DB and not returned to the application.

travelboy
  • 2,647
  • 3
  • 27
  • 37

2 Answers2

2
SET @rank=0;
SELECT Item, @rank:=@rank+1 AS rank
FROM yourTable
ORDER BY Score DESC;

This option uses a session variable to emulate row number functionality, as MySQL does not support analytic functions.

Here is a link to a helpful tutorial which discusses emulating row number in MySQL in more depth.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    @sumit Actually, many other people [beat both of us to it](http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) :-) – Tim Biegeleisen Jan 23 '17 at 03:35
  • Feels embarrassing if it's really that simple :) I was thinking much too complicated... and I didn't know that the additions would happen after the `ORDER BY` – travelboy Jan 23 '17 at 03:38
2

Single statement answer is:

select
    item,
    @rank := @rank + 1 rank
from (
    SELECT *
    FROM yourTable
    ORDER BY Score DESC
) t cross join (select @rank:=0) t2

Without subquery:

SELECT Item, @rank := if(@rank is null, 1, @rank+1) AS rank
FROM yourTable
ORDER BY Score DESC;
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76