window

RANK() Per Group With PARTITION BY

Rank employees by salary within each department using RANK() OVER (PARTITION BY ...).

Overview

RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) assigns a rank within each department, leaving gaps after ties (two employees tied for 1st push the next rank to 3rd). It's the companion to DENSE_RANK and ideal for "top earner per department" style reports where gaps are acceptable.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

RANK() OVER (...) — assigns a rank based on the window ordering.
PARTITION BY department_id — the ranking restarts at 1 for each department.
ORDER BY salary DESC — highest-paid in each department gets rank 1.

RANK vs DENSE_RANK

RANK leaves gaps after ties (1,1,3); DENSE_RANK doesn't (1,1,2). Pick based on whether the gap matters for your ranking semantics.

Variations

Rank movies by budget within each genre:

Movies (Movies/Actors/Ratings)

Loading database engine...

Common Mistakes

Forgetting PARTITION BY. Without it, RANK ranks everyone company-wide instead of per department.
Expecting no gaps. RANK intentionally skips numbers after ties.
Filtering RANK in WHERE. Window functions run after WHERE; wrap in a CTE/subquery to filter on dept_rank.