What is the best way to generate ranks in MYSQL?

Question

What's the best way to get the rank of the rows in addition to the row data in MYSQL?

For instance, say I have a list of students and I want to rank on the GPA. I know I can order by the GPA, but what's the quickest way to have MYSQL return the rank as well in the rowdata I get back?

http://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table — Ciro Santilli Путлер Капут 六四事, Oct 07 '15 at 05:15
Possible duplicate of [MySQL - Get row number on select](http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Ciro Santilli Путлер Капут 六四事, Oct 07 '15 at 05:31

score 6 · Answer 1 · answered Jan 10 '09 at 15:00

6

This will return the students' rank, student ID, and GPA.

set @rownum := 0;
SELECT @rownum := @rownum + 1 AS rank, student_id, gpa 
    FROM `students` ORDER BY gpa DESC

answered Jan 10 '09 at 15:00

William Brendel

30,922
14
70
77

score 5 · Accepted Answer · edited Jun 17 '12 at 14:50

5

This will return the rank as rownum

SELECT @rownum := @rownum + 1 rownum, 
       t.* 
  FROM (SELECT @rownum:=0) r, 
       (SELECT * FROM students ORDER BY gpa DESC) t;

edited Jun 17 '12 at 14:50

Evan Mulawski

53,455
14
114
144

answered Jan 10 '09 at 14:57

Vinko Vrsalovic

253,260
52
326
367

Yours looks much more complicated than William's answer. What's the difference? – Rob Kennedy Jan 10 '09 at 17:57
2

It's a single statement. William's are two separate statements. That's the reason it's more complicated. – Vinko Vrsalovic Jan 10 '09 at 18:39

What is the best way to generate ranks in MYSQL?

2 Answers2

Linked