Ranking Student Scores with MySQL: Table Setup, User Variables, and Window Functions

The author introduces a common requirement: ranking all students by their scores and displaying their positions. After a brief personal note, the tutorial proceeds with the necessary SQL steps.

Database Preparation

Create a table CREATE TABLE `s_score` ( `id` int NOT NULL AUTO_INCREMENT, `score` int NOT NULL DEFAULT 0, `name` varchar(20) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (`id`) );

Insert sample records:

INSERT INTO `s_score` (`name`,`score`) VALUES ('张三',80),('小明',90),('小红',60),('李四',70),('赵武',80),('梁晨',87),('小绿',69),('威廉',69),('大卫',91),('王五',96),('赵六',96),('小五',80),('小龙',88);

Ordinary Implementation (MySQL < 8.0)

Use user variables to simulate ranking:

SELECT name,score, @rank:=@rank+1 `rank` FROM s_score s, (SELECT @rank:=0) q ORDER BY score DESC;

To handle ties, introduce a temporary variable that stores the previous score:

SELECT name,score, CASE WHEN @temp_score=score THEN @rank WHEN @temp_score:=score THEN @rank:=@rank+1 END `rank` FROM s_score s, (SELECT @rank:=0,@temp_score:=NULL) q ORDER BY score DESC;

For ranking with gaps (standard RANK behavior), use an additional increment variable:

SELECT name,score, @rank:=IF(@temp_score=score,@rank,@rank_incr) `rank`, @rank_incr:=@rank_incr+1, @temp_score:=score FROM s_score s, (SELECT @rank:=0,@temp_score:=NULL,@rank_incr:=1) q ORDER BY score DESC;

Using SQL Window Functions (MySQL 8.0+)

The generic syntax is SELECT ranking_function() OVER (ORDER BY column DESC) AS alias . The three main functions are:

RANK() : produces gaps when ties occur.

DENSE_RANK() : no gaps, consecutive ranks.

ROW_NUMBER() : unique sequential numbers regardless of ties.

Example query combining all three:

SELECT name, score, RANK() OVER (ORDER BY score DESC) `rank`, DENSE_RANK() OVER (ORDER BY score DESC) `dense`, ROW_NUMBER() OVER (ORDER BY score DESC) `row` FROM s_score;

The article concludes with references to external documentation and invites readers to share additional implementations.