Using Window Functions in MySQL: From Session Variables and GROUP_CONCAT to Native ROW_NUMBER()

Background – MySQL historically only supported aggregate functions like MAX and AVG, lacking built‑in window (analytic) functions. Developers could write custom UDFs in C, but common work‑arounds involved session variables or GROUP_CONCAT.

Preparation – Three tables are created to illustrate the problem:

CREATE TABLE student (sid INT UNSIGNED NOT NULL, sname VARCHAR(64), PRIMARY KEY (sid)) ENGINE=InnoDB;

CREATE TABLE course (cid INT UNSIGNED NOT NULL, cname VARCHAR(64), PRIMARY KEY (cid)) ENGINE=InnoDB;

CREATE TABLE score (sid INT UNSIGNED NOT NULL, cid INT UNSIGNED NOT NULL, score TINYINT UNSIGNED, PRIMARY KEY (sid, cid)) ENGINE=InnoDB;

Sample data for students, courses, and scores are inserted (10 students, 5 courses, 50 score rows).

MySQL 8.0 Previous Work‑arounds

Session variable method – Assign a rank within each partition by using user‑defined variables:

SELECT b.cname, a.sname, c.score, c.ranking_score
FROM student a, course b,
     (SELECT c.*, IF(@cid = c.cid, @rn := @rn + 1, @rn := 1) AS ranking_score,
             @cid := c.cid AS tmpcid
      FROM (SELECT * FROM score ORDER BY cid, score DESC) c,
           (SELECT @rn := 0, @cid := '') initialize_table) c
WHERE a.sid = c.sid AND b.cid = c.cid AND c.ranking_score <= 3
ORDER BY b.cname, c.ranking_score;

GROUP_CONCAT method – Build a comma‑separated list of scores per course and use FIND_IN_SET to obtain the rank:

SELECT b.cname, a.sname, c.score,
       FIND_IN_SET(c.score, d.gp) AS score_ranking
FROM student a, course b, score c,
     (SELECT cid, GROUP_CONCAT(score ORDER BY score DESC SEPARATOR ',') AS gp
      FROM score GROUP BY cid) d
WHERE a.sid = c.sid AND b.cid = c.cid AND c.cid = d.cid
ORDER BY d.cid, score_ranking;

Both approaches return the top three students per course, but they are cumbersome and error‑prone.

MySQL 8.0 Window Functions

MySQL 8.0 introduces native window functions, allowing a concise ranking query:

SELECT b.cname, a.sname, c.score,
       ROW_NUMBER() OVER (PARTITION BY b.cname ORDER BY c.score DESC) AS score_rank
FROM student AS a, course AS b, score AS c
WHERE a.sid = c.sid AND b.cid = c.cid
HAVING score_rank <= 3;

This produces the same result as the previous methods with far less code.

Finding Failing Students

To list the bottom two students (score < 60) in the MySQL and DBLE courses, the query uses ROW_NUMBER() with ASC ordering and filters by course IDs (20192001 for MySQL, 20192005 for DBLE):

SELECT b.cname, a.sname, c.score,
       ROW_NUMBER() OVER (PARTITION BY b.cid ORDER BY c.score ASC) AS score_ranking
FROM student AS a, course AS b, score AS c
WHERE a.sid = c.sid AND b.cid = c.cid
  AND b.cid IN (20192001, 20192005)
  AND c.score < 60
HAVING score_ranking < 3;

The result shows the lowest‑scoring students for the specified courses.

Conclusion

The article demonstrates how ranking can be achieved in MySQL before version 8.0 using session variables or GROUP_CONCAT, and how the introduction of native window functions simplifies these tasks. Readers are encouraged to explore other window functions for further analytical queries.

Community Announcement

A call for submissions to the third community technical content round, focusing on MySQL, DBLE, and DTLE topics, with rewards including JD cards and custom merchandise.