Window functions (ROW_NUMBER) in MySQL

Introduction

ROW_NUMBER helps you give a unique number to each row in a group or whole table. It makes it easy to order and pick rows.

You want to number rows in a list, like ranking players by score.

You need to pick the top N items per category, like top 3 salespeople per region.

You want to remove duplicates but keep the first entry.

You want to create a sequence number for rows in a report.

Syntax

MySQL

ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

PARTITION BY divides rows into groups. Numbering restarts for each group.

ORDER BY decides the order of numbering inside each group.

Examples

Numbers all players by score from highest to lowest.

MySQL

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM players;

Numbers employees within each department by salary.

MySQL

SELECT department, employee, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;

Numbers products alphabetically within each category.

MySQL

SELECT product, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY product) AS product_num FROM products;

Sample Program

This creates a sales table, adds some data, then numbers sales by region with the highest amount first.

MySQL

CREATE TABLE sales (
  id INT,
  region VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
(1, 'East', 100),
(2, 'West', 200),
(3, 'East', 150),
(4, 'West', 50),
(5, 'East', 120);

SELECT id, region, amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

Important Notes

ROW_NUMBER always gives unique numbers, even if values tie.

If you want ties to share the same number, consider RANK() or DENSE_RANK() instead.

Summary

ROW_NUMBER() assigns a unique number to each row in order.

Use PARTITION BY to restart numbering for groups.

ORDER BY controls the numbering order inside groups.