Number Rows Within Groups Using ROW_NUMBER

Assign sequential integers to rows within a partition without collapsing the result set the way `GROUP BY` does.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Rank employees by salary within each department

PARTITION BY department restarts the counter for each department. ORDER BY salary DESC determines which employee gets rank 1 (the highest earner). Each department's numbering is independent — both Ada and Karen receive row_num 1 within their respective groups.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO
  employees (id, name, department, salary)
VALUES
  (1, 'Ada', 'Engineering', 90000),
  (2, 'Grace', 'Engineering', 85000),
  (3, 'Linus', 'Engineering', 80000),
  (4, 'Karen', 'Finance', 75000),
  (5, 'Steve', 'Finance', 70000);

Validated query Shared across supported engines.

SQL

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

Expected result Returned rows for the shared example.

name	department	salary	row_num
Ada	Engineering	90000	1
Grace	Engineering	85000	2
Linus	Engineering	80000	3
Karen	Finance	75000	1
Steve	Finance	70000	2

Output is identical across all supported engine versions.

Useful when

Where this command helps.

picking the top row per group
assigning stable sequence numbers for ranking or pagination

Explanation

What the command is doing.

ROW_NUMBER() is a window function that assigns a unique sequential number to each row within a defined partition, ordered by the specified column. Unlike GROUP BY, the original rows are preserved — the number is just an additional computed column. This is commonly used for ranking, pagination, and deduplication.

Number Rows Within Groups Using ROW_NUMBER

Rank employees by salary within each department

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.