ROW_NUMBER

Concepts covered: Row Numbering

PARTITION BY decides which rows share a window. ORDER BY inside OVER decides the order of rows within it, and a family of functions depends on that order entirely. ROW_NUMBER() is the first: it walks the window in the order given and hands out 1, 2, 3 and so on, never repeating a number and never skipping one. ROW_NUMBER() OVER (ORDER BY salary DESC) numbers the 6 employees from highest earner to lowest: Carol 1, Alice 2, Frank 3, Eve and Grace at 4 and 5 in some order, Dave 6. The function takes no argument because it reads nothing from the row; the position in the window is the whole output.

Add a partition and the numbering restarts at 1 inside each one. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) gives Carol 1 and Alice 2 in Engineering, Eve 1 and Dave 2 in Sales, Frank 1 and Grace 2 in Marketing. The partition is formed first, the order is applied within it, and the numbers are assigned last. That sequence (partition, then order, then compute) is the one to picture for every function in the rest of the lesson.

SELECT emp_name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, emp_name) AS company_pos,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_pos
FROM employees
ORDER BY department, dept_pos

Both columns come from 1 query with no join, and they answer different questions. company_pos is a standing across the company, and dept_pos is a standing within a team. Change the ORDER BY inside the second OVER to hire_date and dept_pos becomes seniority within the department. The partition and the 6 rows stay as they were.

The animation shows the per-department numbering being assigned: rows sorted into partitions, each partition sorted by salary descending, and the counter starting over at 1 as each new partition opens.

SELECT
  emp_name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_pos
FROM employees

Ties and the order that is not there

Eve and Grace both earn 90,000. ORDER BY salary DESC puts them next to each other and says nothing about which comes first, so ROW_NUMBER gives one of them 4 and the other 5 by whatever order the engine happened to read the rows in. The same query can return Eve 4 today and Grace 4 after the table is rewritten or the engine picks a different plan. The fix is a tiebreaker: ORDER BY salary DESC, emp_name makes the sequence fully determined, and ORDER BY salary DESC, employee_id does the same with an id that never changes. Omit ORDER BY altogether and ROW_NUMBER still runs, numbering rows in whatever order they arrived, which serves exactly 1 purpose: stamping a unique but meaningless id on rows that have none.

The top earner in each department

The question ROW_NUMBER exists for is first-per-group: the highest-paid person in each department, the latest order per customer, the most recent reading per sensor. The window numbers the rows and a filter keeps the 1s. The filter cannot sit in the same query's WHERE, because WHERE runs before the number exists, so the numbering query becomes a derived table (a query placed in FROM and given a name) and the filter goes on the outer query.

SELECT emp_name, department, salary
FROM (
  SELECT emp_name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_pos
  FROM employees
) ranked
WHERE dept_pos = 1

Change = 1 to <= 2 and the query returns the top 2 per department, which is 6 rows here because every department has 2 people, and would be 2 rows per department on a department of 40. GROUP BY department with MAX(salary) finds the 3 numbers, and getting the names back requires a join from the 3 maxima to the detail rows; the window returns the names directly, and handles top 2, top 5 or top 10 by changing 1 constant.

The puzzle below is the inner query, split into clauses. Two tiles are distractors from the GROUP BY habit, and the grammar fixes the order of the rest.

Assemble the inner query that numbers employees within their department from highest salary to lowest, naming the number dept_pos. The clauses go in the order the grammar requires, and 2 tiles do not belong.

A second everyday use is pagination. Number the rows with ROW_NUMBER() OVER (ORDER BY hire_date, employee_id) AS row_pos and filter the outer query on row_pos BETWEEN 21 AND 40, and the result is the second page of 20. The order includes a unique column, so page 2 contains the same rows on every request. LIMIT with OFFSET on a non-unique order does not guarantee that.

About this lesson

This section is part of the Window Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.