Menu
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?
479
The rank of a row is increased by one from the number of distinct rank values which come before the row. The syntax of the DENSE_RANK() function is as follows: DENSE_RANK() OVER ( PARTITION BY <expression>[{,<expression>...}] ORDER BY <expression> [ASC|DESC], [{,<expression>...}] )
To partition rows and rank them by their position within the partition, use the RANK() function with the PARTITION BY clause. SQL's RANK() function allows us to add a record's position within the result set or within each partition. In our example, we rank rows within a partition.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
