CodeRang
SQL topics

SQL window functions with ROW_NUMBER examples

SQL window functions calculate values across related rows without collapsing those rows into a single grouped result. This ROW_NUMBER example shows how PARTITION BY and window ordering solve a top-N-per-group problem.

Updated · SQL Arena team

Window functions trackAll problems

What you will practice

  • ROW_NUMBER, RANK, and DENSE_RANK
  • PARTITION BY and window ordering
  • LAG and LEAD
  • Running totals with SUM() OVER
  • Top-N rows per group

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find the two highest-paid employees in each department

Return the two employees with the highest salary in every department. Output employee_name, department_name, and salary. If two employees have the same salary, use the employee id as an additional ordering key.

Query 1PostgreSQLv16
WITH ranked AS (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY department_id ORDER BY salary DESC, id
  ) AS rn
  FROM employees e
)
SELECT r.employee_name, d.department_name, r.salary
FROM ranked r JOIN departments d ON d.id = r.department_id
WHERE r.rn <= 2 ORDER BY d.id, r.rn;
Submit
Resultdepartmentsemployees
Answer accepted

How SQL window functions work with ROW_NUMBER

A regular ORDER BY salary DESC ranks the company as one list, which is not what the task asks for. GROUP BY does not solve it either. Grouping is useful when you want one aggregate row per department, such as MAX(salary), but here we need to keep individual employee rows and choose two of them from every department.

The ranked CTE calculates ROW_NUMBER() for each employee. PARTITION BY department_id restarts the numbering for every department. ORDER BY salary DESC places the highest salary first inside that department. Adding id as a second sort key removes ambiguity when two salaries are equal, so the numbering remains deterministic.

The outer query filters the generated row numbers with rn <= 2. A separate query level is useful because a window value is calculated after the WHERE phase of the same SELECT. The query then joins departments to return a readable department name. The final ORDER BY controls output presentation; it does not decide which employees belong to the top two.

A SQL window function operates over the rows defined by its OVER clause. PARTITION BY separates rows into independent partitions, while ORDER BY inside the window determines the sequence used by functions such as ROW_NUMBER, RANK, or running aggregates. Unlike GROUP BY, window functions keep the original rows in the result. That makes them useful for rankings, running totals, comparisons with previous or next rows, and top-N-per-group queries where individual records still need to be returned.

SQLPostgreSQL v16
WITH ranked AS (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY department_id ORDER BY salary DESC, id
  ) AS rn
  FROM employees e
)
SELECT r.employee_name, d.department_name, r.salary
FROM ranked r JOIN departments d ON d.id = r.department_id
WHERE r.rn <= 2 ORDER BY d.id, r.rn;
Query result
employee_namedepartment_namesalary
NoraDesign81000
MiaDesign72000
EthanFinance93000
IvyFinance87000
ZoeSupport66000
LiamSupport61000

Common SQL window function mistakes

01

Leaving out PARTITION BY

Without PARTITION BY department_id, ROW_NUMBER creates one sequence across the entire employee table. Filtering to the first two rows would return only two employees overall instead of two from each department.

02

Using RANK when exactly N rows are required

RANK can return more than N rows when several records tie at the boundary. ROW_NUMBER always assigns a distinct position, which makes it a better fit when the requirement literally says to return exactly two records per group.

03

Using an incomplete window order

If salary is the only ordering expression, equal salaries have no defined internal order. Add a stable tie-breaker such as a primary key when the exact row sequence matters.

Practice problems on this topic

Window functions track

After this ROW_NUMBER exercise, useful window functions practice problems include numbering purchases per user, measuring gaps between events, detecting session starts, calculating moving averages, and building running revenue totals. SQL Arena groups the topic in the “Window Functions from Start to Finish” track. The trainer contains 1,192 tasks, with separate browser sandboxes for PostgreSQL, MySQL, ClickHouse, and BigQuery. Each task is checked against hidden data rather than only the visible sample, so a query has to express the general rule. The free plan requires no card and includes 292 tasks across all four dialects. EXPLAIN and schema ER diagrams are available when you need to inspect the query or tables more closely.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What are SQL window functions?

SQL window functions calculate a value using a set of rows related to the current row while keeping each row in the result. They use an OVER clause and can include PARTITION BY and ORDER BY.

How do SQL window functions work?

The OVER clause defines the window used for the calculation. PARTITION BY can split the data into separate groups, and ORDER BY can define the sequence within each group.

What is ROW_NUMBER in SQL window functions?

ROW_NUMBER assigns a unique sequential number to rows according to the ordering defined inside OVER. If PARTITION BY is present, numbering restarts for every partition.

What is the difference between SQL window functions and GROUP BY?

GROUP BY reduces multiple rows to one row per group. Window functions preserve the individual rows and add calculations based on other rows in the same window.

How do you get the top N rows per group with SQL window functions?

A common approach is to calculate ROW_NUMBER in a CTE or subquery, partition by the grouping column, and order each partition by the ranking criterion. The outer query then filters the generated row number to values less than or equal to N.

See also

Practice SQL window functions with exercises

Move from ROW_NUMBER to ranking, event gaps, running totals, and moving windows in SQL Arena. For a structured path through the topic, continue with the “Window Functions from Start to Finish” track.

Practice window functions

More practice from CodeRang