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 OurBigBook.com, 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 OurBigBook.com, 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

31,712
14
72
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

54,662
15
117
144

answered Jan 10 '09 at 14:57

Vinko Vrsalovic

330,807
53
334
373

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