CODEWITHSUNDEEP
mysqlsql
courage
courage

Reputation: 125

How to Rank scores Using sql Rank

I have been trying to rank some scores in the database but the output is wrong. i have done it before but I have forgotten how i did it.

sql

 SELECT `total_score`, FIND_IN_SET( `total_score`, (
SELECT GROUP_CONCAT( DISTINCT `total`
ORDER BY `total_score` DESC ) FROM `table` WHERE `class`="class" AND `session`="2018/2019")
) AS rank
FROM `table` WHERE `class`="class" AND `session`="2018/2019";

This are the data am working with for now scores

Output
Output

From the output, the highest score is the one getting the lowest rank. I used group_concat because i want to be able to select some range of data with same class and session and then rank the total scores and i want the highest to be the first. Hope that makes any sense.

The data i was given i really large and i just want to be able to select the student classes, the academic session or whatever it is, their total score for that particular session and rank them.

Upvotes: 1

Views: 1291

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

In older versions of MySQL, I would recommend using variables:

SELECT total, (@rank := @rank + 1) as rank
FROM (SELECT DISTINCT CASTt.total + 0 as total
      FROM `table` t
      WHERE class = 'class' AND session = '2018/2019'
      ORDER BY total ASC
     ) t CROSS JOIN
     (SELECT @rank := 0) params;

In MySQL 8+, you would do:

SELECT DISTINCT t.total, DENSE_RANK() OVER (ORDER BY total + 0 ASC) as the_rank
FROM `table` t
WHERE class = 'class' AND session = '2018/2019'

Upvotes: 0

Related Questions