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