1. Tutorials
  2. SQL Window Functions in MySQL: ROW_NUMBER, RANK, DENSE_RANK, Running Total, LAG and LEAD

SQL Window Functions in MySQL: ROW_NUMBER, RANK, DENSE_RANK, Running Total, LAG and LEAD

Submitted by jaypabs on Tuesday, November 3, 2026 - 09:22.

Ranking students by grade, showing the top 3 products per category, computing a running total of sales, or comparing this month with last month... these are reports you will be asked to build in almost every school system, inventory system or capstone project. Many beginners solve them with complicated subqueries, or by pulling all the rows into PHP or Python and looping. There is a much cleaner way: SQL window functions.

In this tutorial you will learn what window functions are and how to use the most useful ones in MySQL: ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER for running totals, LAG() and LEAD() for month-over-month changes, moving averages, NTILE() and FIRST_VALUE(). Every query below was run on a MySQL-compatible server (MariaDB 11.8), and the result tables are copied straight from the console so you can compare them with your own output.

Requirements

  • MySQL 8.0 or newer, or MariaDB 10.2 or newer. Older MySQL 5.7 (still bundled with some old XAMPP versions) does not support window functions.
  • phpMyAdmin, MySQL Workbench, HeidiSQL, or the MySQL command line.
  • Basic SQL: SELECT, WHERE, ORDER BY and GROUP BY.

Not sure which version you have? Run SELECT VERSION();.

Step 1: Create the Sample Database

Run this script to create a school_window database with two tables: exam scores of two sections, and monthly sales of two store branches. Notice that some students have the same score on purpose. Ties are where the ranking functions behave differently.

  1. DROP DATABASE IF EXISTS school_window;
  2. CREATE DATABASE school_window;
  3. USE school_window;
  4.  
  5. CREATE TABLE exam_scores (
  6.   id INT AUTO_INCREMENT PRIMARY KEY,
  7.   student VARCHAR(50) NOT NULL,
  8.   SECTION VARCHAR(20) NOT NULL,
  9.   score INT NOT NULL
  10. );
  11.  
  12. INSERT INTO exam_scores (student, SECTION, score) VALUES
  13. ('Ana Reyes',      'BSIT-1A', 95),
  14. ('Ben Cruz',       'BSIT-1A', 88),
  15. ('Carla Santos',   'BSIT-1A', 95),
  16. ('Dan Lopez',      'BSIT-1A', 79),
  17. ('Ella Garcia',    'BSIT-1A', 88),
  18. ('Felix Tan',      'BSIT-1B', 91),
  19. ('Gina Ramos',     'BSIT-1B', 84),
  20. ('Hector Lim',     'BSIT-1B', 91),
  21. ('Ivy Mendoza',    'BSIT-1B', 76);
  22.  
  23. CREATE TABLE monthly_sales (
  24.   id INT AUTO_INCREMENT PRIMARY KEY,
  25.   branch VARCHAR(20) NOT NULL,
  26.   sale_month DATE NOT NULL,
  27.   amount DECIMAL(10,2) NOT NULL
  28. );
  29.  
  30. INSERT INTO monthly_sales (branch, sale_month, amount) VALUES
  31. ('Manila', '2026-01-01', 42000.00),
  32. ('Manila', '2026-02-01', 38500.00),
  33. ('Manila', '2026-03-01', 45250.00),
  34. ('Manila', '2026-04-01', 51000.00),
  35. ('Manila', '2026-05-01', 49800.00),
  36. ('Manila', '2026-06-01', 56300.00),
  37. ('Cebu',   '2026-01-01', 27500.00),
  38. ('Cebu',   '2026-02-01', 30100.00),
  39. ('Cebu',   '2026-03-01', 29400.00),
  40. ('Cebu',   '2026-04-01', 33750.00),
  41. ('Cebu',   '2026-05-01', 36200.00),
  42. ('Cebu',   '2026-06-01', 35900.00);

What Is a Window Function?

A normal aggregate with GROUP BY squeezes many rows into one row per group. For example, this gives the average score per section:

  1. SELECT SECTION, ROUND(AVG(score), 2) AS section_avg
  2. FROM exam_scores
  3. GROUP BY SECTION;

Output:

+---------+-------------+
| section | section_avg |
+---------+-------------+
| BSIT-1A |       89.00 |
| BSIT-1B |       85.50 |
+---------+-------------+

But what if you want to show every student and, beside each one, the average of their section? That is exactly what a window function does. It calculates over a set of rows (the "window") but keeps every row in the result. You turn an aggregate into a window function by adding OVER (...):

  1. SELECT student, SECTION, score,
  2.        ROUND(AVG(score) OVER (PARTITION BY SECTION), 2) AS section_avg,
  3.        score - ROUND(AVG(score) OVER (PARTITION BY SECTION), 2) AS diff_from_avg
  4. FROM exam_scores
  5. ORDER BY SECTION, score DESC, student;

Output:

+--------------+---------+-------+-------------+---------------+
| student      | section | score | section_avg | diff_from_avg |
+--------------+---------+-------+-------------+---------------+
| Ana Reyes    | BSIT-1A |    95 |       89.00 |          6.00 |
| Carla Santos | BSIT-1A |    95 |       89.00 |          6.00 |
| Ben Cruz     | BSIT-1A |    88 |       89.00 |         -1.00 |
| Ella Garcia  | BSIT-1A |    88 |       89.00 |         -1.00 |
| Dan Lopez    | BSIT-1A |    79 |       89.00 |        -10.00 |
| Felix Tan    | BSIT-1B |    91 |       85.50 |          5.50 |
| Hector Lim   | BSIT-1B |    91 |       85.50 |          5.50 |
| Gina Ramos   | BSIT-1B |    84 |       85.50 |         -1.50 |
| Ivy Mendoza  | BSIT-1B |    76 |       85.50 |         -9.50 |
+--------------+---------+-------+-------------+---------------+

The PARTITION BY section part splits the rows into groups, just like GROUP BY, but without collapsing them. If you write OVER () with nothing inside, the window is the whole result set.

The general syntax looks like this:

function_name(...) OVER (
    PARTITION BY column     -- optional: restart the calculation for each group
    ORDER BY column         -- optional: order of rows inside the window
    ROWS BETWEEN ... AND ...  -- optional: which rows around the current row to include
)

Step 2: ROW_NUMBER, RANK and DENSE_RANK

These three functions number rows in order, and they only disagree on how to handle ties:

  1. SELECT student, score,
  2.        ROW_NUMBER() OVER (ORDER BY score DESC, student) AS row_num,
  3.        RANK()       OVER (ORDER BY score DESC) AS rnk,
  4.        DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
  5. FROM exam_scores
  6. ORDER BY score DESC, student;

Output:

+--------------+-------+---------+-----+-----------+
| student      | score | row_num | rnk | dense_rnk |
+--------------+-------+---------+-----+-----------+
| Ana Reyes    |    95 |       1 |   1 |         1 |
| Carla Santos |    95 |       2 |   1 |         1 |
| Felix Tan    |    91 |       3 |   3 |         2 |
| Hector Lim   |    91 |       4 |   3 |         2 |
| Ben Cruz     |    88 |       5 |   5 |         3 |
| Ella Garcia  |    88 |       6 |   5 |         3 |
| Gina Ramos   |    84 |       7 |   7 |         4 |
| Dan Lopez    |    79 |       8 |   8 |         5 |
| Ivy Mendoza  |    76 |       9 |   9 |         6 |
+--------------+-------+---------+-----+-----------+
  • ROW_NUMBER() always gives a unique number: 1, 2, 3, 4... Tied rows still get different numbers. That is why we added student as a second sort column, so the order of tied rows is predictable instead of random.
  • RANK() gives tied rows the same rank, then skips numbers. Ana and Carla are both rank 1, so the next student is rank 3. This is how sports and contests usually rank.
  • DENSE_RANK() gives tied rows the same rank without gaps: 1, 1, 2, 2, 3. Use it when you want "1st, 2nd, 3rd place" with no missing places.

Add PARTITION BY to rank inside each group. Here every section gets its own 1st, 2nd and 3rd place:

  1. SELECT SECTION, student, score,
  2.        DENSE_RANK() OVER (PARTITION BY SECTION ORDER BY score DESC) AS place
  3. FROM exam_scores
  4. ORDER BY SECTION, place, student;

Output:

+---------+--------------+-------+-------+
| section | student      | score | place |
+---------+--------------+-------+-------+
| BSIT-1A | Ana Reyes    |    95 |     1 |
| BSIT-1A | Carla Santos |    95 |     1 |
| BSIT-1A | Ben Cruz     |    88 |     2 |
| BSIT-1A | Ella Garcia  |    88 |     2 |
| BSIT-1A | Dan Lopez    |    79 |     3 |
| BSIT-1B | Felix Tan    |    91 |     1 |
| BSIT-1B | Hector Lim   |    91 |     1 |
| BSIT-1B | Gina Ramos   |    84 |     2 |
| BSIT-1B | Ivy Mendoza  |    76 |     3 |
+---------+--------------+-------+-------+

Step 3: Top N per Group

"Show the top 2 students per section" (or the top 3 best-selling products per category) is one of the most asked SQL questions. You cannot use a window function directly inside WHERE, because WHERE runs before the window is calculated. The fix is to compute the row number in a CTE (a WITH query) and filter outside it:

  1. WITH ranked AS (
  2.   SELECT SECTION, student, score,
  3.          ROW_NUMBER() OVER (PARTITION BY SECTION ORDER BY score DESC, student) AS rn
  4.   FROM exam_scores
  5. )
  6. SELECT SECTION, student, score
  7. FROM ranked
  8. WHERE rn <= 2
  9. ORDER BY SECTION, rn;

Output:

+---------+--------------+-------+
| section | student      | score |
+---------+--------------+-------+
| BSIT-1A | Ana Reyes    |    95 |
| BSIT-1A | Carla Santos |    95 |
| BSIT-1B | Felix Tan    |    91 |
| BSIT-1B | Hector Lim   |    91 |
+---------+--------------+-------+

Change ROW_NUMBER() to DENSE_RANK() if you want to include everyone who is tied with the 2nd place.

Step 4: Running Total with SUM() OVER

A running (cumulative) total adds each row to all the rows before it. Add ORDER BY inside OVER and a frame that starts at the first row:

  1. SELECT branch, DATE_FORMAT(sale_month, '%Y-%m') AS MONTH, amount,
  2.        SUM(amount) OVER (
  3.          PARTITION BY branch
  4.          ORDER BY sale_month
  5.          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  6.        ) AS running_total
  7. FROM monthly_sales
  8. ORDER BY branch DESC, sale_month;

Output:

+--------+---------+----------+---------------+
| branch | month   | amount   | running_total |
+--------+---------+----------+---------------+
| Manila | 2026-01 | 42000.00 |      42000.00 |
| Manila | 2026-02 | 38500.00 |      80500.00 |
| Manila | 2026-03 | 45250.00 |     125750.00 |
| Manila | 2026-04 | 51000.00 |     176750.00 |
| Manila | 2026-05 | 49800.00 |     226550.00 |
| Manila | 2026-06 | 56300.00 |     282850.00 |
| Cebu   | 2026-01 | 27500.00 |      27500.00 |
| Cebu   | 2026-02 | 30100.00 |      57600.00 |
| Cebu   | 2026-03 | 29400.00 |      87000.00 |
| Cebu   | 2026-04 | 33750.00 |     120750.00 |
| Cebu   | 2026-05 | 36200.00 |     156950.00 |
| Cebu   | 2026-06 | 35900.00 |     192850.00 |
+--------+---------+----------+---------------+

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the first row of this branch up to the current row". Because of PARTITION BY branch, the total starts again at zero for Cebu. This is perfect for year-to-date sales, account balances, or the running balance of a customer's credit.

Step 5: Compare with the Previous Row Using LAG and LEAD

LAG(column) returns the value from the previous row, and LEAD(column) returns the value from the next row. That makes month-over-month growth easy:

  1. SELECT DATE_FORMAT(sale_month, '%Y-%m') AS MONTH, amount,
  2.        LAG(amount) OVER (ORDER BY sale_month) AS prev_month,
  3.        amount - LAG(amount) OVER (ORDER BY sale_month) AS change_amt,
  4.        ROUND((amount - LAG(amount) OVER (ORDER BY sale_month))
  5.              / LAG(amount) OVER (ORDER BY sale_month) * 100, 1) AS change_pct,
  6.        LEAD(amount) OVER (ORDER BY sale_month) AS next_month
  7. FROM monthly_sales
  8. WHERE branch = 'Manila'
  9. ORDER BY sale_month;

Output:

+---------+----------+------------+------------+------------+------------+
| month   | amount   | prev_month | change_amt | change_pct | next_month |
+---------+----------+------------+------------+------------+------------+
| 2026-01 | 42000.00 |       NULL |       NULL |       NULL |   38500.00 |
| 2026-02 | 38500.00 |   42000.00 |   -3500.00 |       -8.3 |   45250.00 |
| 2026-03 | 45250.00 |   38500.00 |    6750.00 |       17.5 |   51000.00 |
| 2026-04 | 51000.00 |   45250.00 |    5750.00 |       12.7 |   49800.00 |
| 2026-05 | 49800.00 |   51000.00 |   -1200.00 |       -2.4 |   56300.00 |
| 2026-06 | 56300.00 |   49800.00 |    6500.00 |       13.1 |       NULL |
+---------+----------+------------+------------+------------+------------+

The first month has no previous month, so LAG returns NULL, and anything calculated from it is also NULL. If you prefer zero, give a default value as the third argument: LAG(amount, 1, 0). The second argument is how many rows back to look, so LAG(amount, 12) would compare with the same month last year in a monthly table.

Step 6: Moving Average and the WINDOW Clause

A 3-month moving average smooths out the ups and downs in sales. The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes the current month and the two months before it. When you use the same window more than once, you can give it a name with the WINDOW clause instead of repeating it:

  1. SELECT branch, DATE_FORMAT(sale_month, '%Y-%m') AS MONTH, amount,
  2.        ROUND(AVG(amount) OVER w, 2) AS moving_avg_3m,
  3.        COUNT(*) OVER w AS months_in_avg
  4. FROM monthly_sales
  5. WHERE branch = 'Cebu'
  6. WINDOW w AS (PARTITION BY branch ORDER BY sale_month
  7.              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
  8. ORDER BY sale_month;

Output:

+--------+---------+----------+---------------+---------------+
| branch | month   | amount   | moving_avg_3m | months_in_avg |
+--------+---------+----------+---------------+---------------+
| Cebu   | 2026-01 | 27500.00 |      27500.00 |             1 |
| Cebu   | 2026-02 | 30100.00 |      28800.00 |             2 |
| Cebu   | 2026-03 | 29400.00 |      29000.00 |             3 |
| Cebu   | 2026-04 | 33750.00 |      31083.33 |             3 |
| Cebu   | 2026-05 | 36200.00 |      33116.67 |             3 |
| Cebu   | 2026-06 | 35900.00 |      35283.33 |             3 |
+--------+---------+----------+---------------+---------------+

The first two rows average fewer than three months because there is no earlier data yet. The months_in_avg column makes that visible. In a real report you could hide the average until it reaches 3.

Step 7: NTILE and FIRST_VALUE

NTILE(n) divides the ordered rows into n groups that are as equal as possible, which is handy for top/middle/bottom brackets or quartiles. FIRST_VALUE() returns the first value in the window, such as the top scorer of each section:

  1. SELECT student, score,
  2.        NTILE(3) OVER (ORDER BY score DESC, student) AS bracket,
  3.        FIRST_VALUE(student) OVER (PARTITION BY SECTION ORDER BY score DESC, student) AS section_topnotcher
  4. FROM exam_scores
  5. ORDER BY score DESC, student;

Output:

+--------------+-------+---------+--------------------+
| student      | score | bracket | section_topnotcher |
+--------------+-------+---------+--------------------+
| Ana Reyes    |    95 |       1 | Ana Reyes          |
| Carla Santos |    95 |       1 | Ana Reyes          |
| Felix Tan    |    91 |       1 | Felix Tan          |
| Hector Lim   |    91 |       2 | Felix Tan          |
| Ben Cruz     |    88 |       2 | Ana Reyes          |
| Ella Garcia  |    88 |       2 | Ana Reyes          |
| Gina Ramos   |    84 |       3 | Felix Tan          |
| Dan Lopez    |    79 |       3 | Ana Reyes          |
| Ivy Mendoza  |    76 |       3 | Felix Tan          |
+--------------+-------+---------+--------------------+

Common Mistakes

  • Using a window function in WHERE. It fails with an error. MariaDB, for example, answers ERROR 4015 (HY000): Window function is allowed only in SELECT list and ORDER BY clause. Wrap the query in a CTE or subquery and filter outside, like in Step 3.
  • Forgetting a tie-breaker in ROW_NUMBER. Without a unique second sort column, tied rows can come back in a different order each time.
  • Mixing up the outer ORDER BY and the one in OVER. The ORDER BY inside OVER controls the calculation. The final ORDER BY controls how the result is displayed. You usually need both.
  • Running totals with duplicate dates. If you leave out the ROWS frame, the default frame is RANGE, which treats rows with the same date as one group and gives them the same total. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to add one row at a time. Here is the difference with two rows on the same date:
    1. SELECT x, d,
    2.        SUM(x) OVER (ORDER BY d) AS default_frame,
    3.        SUM(x) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
    4. FROM (SELECT 1 AS x, '2026-01-01' AS d
    5.       UNION ALL SELECT 2, '2026-01-01'
    6.       UNION ALL SELECT 3, '2026-01-02') AS t
    7. ORDER BY d, x;

    Output:

    +------+------------+---------------+------------+
| x    | d          | default_frame | rows_frame |
+------+------------+---------------+------------+
|    1 | 2026-01-01 |             3 |          1 |
|    2 | 2026-01-01 |             3 |          3 |
|    3 | 2026-01-02 |             6 |          6 |
+------+------------+---------------+------------+
  • Running on MySQL 5.7. If you get a syntax error near OVER, check your version. Upgrade to MySQL 8 or use a recent MariaDB.

Conclusion

Window functions let you add rankings, running totals and comparisons to your queries while keeping every row, and they replace a lot of messy subqueries and loops in your application code. Start with ROW_NUMBER, RANK and SUM() OVER, since they cover most reports, then add LAG, LEAD and moving averages when you need trends. Try them on your own project's tables, for example ranking your best-selling products or showing a running balance per customer. You can download the SQL script with the sample data and all queries below.

Happy coding!

Tags
SQL
MYSQL
database
sql tutorial