Skip to content
Quantoros

What does PARTITION BY do in a window function?

SQLLevel: IntermediateWindow Functions

What does PARTITION BY do in a window function?

Hints
  • Which rows belong to the partition and frame for each current row?
  • Check ordering, ties, frame boundaries and the difference from GROUP BY.

Answer

PARTITION BY divides the rows into groups (windows) for the function to operate within. It's like GROUP BY but without collapsing rows.

-- Rank employees within each department
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Without PARTITION BY, the window is the entire result set. With PARTITION BY department, the ranking restarts at 1 for each department.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions