Tuesday, March 15, 2011

What is the best way to generate ranks in MYSQL?

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?

From stackoverflow
  • 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;
    
    Rob Kennedy : Yours looks much more complicated than William's answer. What's the difference?
    Vinko Vrsalovic : It's a single statement. William's are two separate statements. That's the reason it's more complicated.
  • 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
    

0 comments:

Post a Comment