Ranking
Sales wants to run a small bonus program: the top-paid person in each department gets recognized first, then second, then third. They don’t want the salaries sorted — they want an actual rank number sitting on each row, per department.
Can we rank employees by salary within each department?
Easy, right? ORDER BY department, salary DESC.
That sorts the rows into the right order. But sorting isn't the same as ranking — nothing in that result actually says '#1' or '#2' anywhere. You want a column with the rank in it.
You already know the shape from the last lesson: PARTITION BY splits the table into groups — here, one group per department. What’s new is what goes inside each group: instead of an aggregate like AVG, you use ROW_NUMBER(), and you add an ORDER BY inside the OVER (...) clause — that ordering is what decides who’s #1 within each partition.
Where does this row land, within its group?
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
WHERE department = 'Sales'
ORDER BY salary DESC;| name | department | salary | salary_rank |
|---|---|---|---|
| Lena Fischer | Sales | 150000 | 1 |
| Carlos Diaz | Sales | 98000 | 2 |
| Yuki Tanaka | Sales | 95000 | 3 |
Clean. Sales has no ties, so 1, 2, 3 reads exactly the way you’d expect. Now let’s point the same query at Engineering.
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;| name | department | salary | salary_rank |
|---|---|---|---|
| Meera Rao | Engineering | 165000 | 1 |
| Sam Okafor | Engineering | 128000 | 2 |
| Priya Nair | Engineering | 128000 | 3 |
| Tom Becker | Engineering | 121000 | 4 |
| Ken Watanabe | Engineering | 121000 | 5 |
Wait — Sam and Priya both make 128000. Why does Sam get rank 2 and Priya rank 3? That makes it look like Sam earns more.
Good catch. He doesn't — they're tied. ROW_NUMBER doesn't know what a tie is. It just counts off rows in order: first, second, third, no matter what the values are. If two rows are exactly equal and you haven't told it how to break the tie, which one comes first is arbitrary.
So if I actually want ties to show up as ties, ROW_NUMBER is the wrong function.
Exactly. There are two more ranking functions, and the difference between them is the best thing in this whole chapter.
RANK() and DENSE_RANK() both give tied rows the exact same rank. They only disagree about what happens after a tie:
RANK()leaves a gap — if two rows tie for 2nd, the next row is ranked 4th, not 3rd. It’s still counting rows, just crediting ties with the same number.DENSE_RANK()never leaves a gap — after two rows tie for 2nd, the next distinct salary is simply 3rd.
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC, name;| name | department | salary | row_num | rnk | dense_rnk |
|---|---|---|---|---|---|
| Meera Rao | Engineering | 165000 | 1 | 1 | 1 |
| Priya Nair | Engineering | 128000 | 2 | 2 | 2 |
| Sam Okafor | Engineering | 128000 | 3 | 2 | 2 |
| Ken Watanabe | Engineering | 121000 | 4 | 4 | 3 |
| Tom Becker | Engineering | 121000 | 5 | 4 | 3 |
Watch what happens after the first tie. Priya and Sam both take rank 2 under both functions. But the next row — salary 121000 — gets 4 under RANK (it’s the 4th row overall, ties or not) and 3 under DENSE_RANK (it’s the 3rd distinct salary). Neither is wrong — they’re answering slightly different questions: “what place is this row in” versus “how many distinct salary tiers are above this one.”
One more thing worth noticing: ROW_NUMBER’s ORDER BY has , name tacked onto the end, as a tiebreaker — otherwise which of Priya or Sam gets 2 versus 3 is left to SQL to decide, and that’s not guaranteed to be stable. RANK and DENSE_RANK don’t need that crutch: since tied rows get the same number either way, there’s nothing left to decide.
Running…
Same three ties show up again in Product (Jamal and Sara, both 105000). Everywhere there’s no tie, all three functions agree completely — the disagreement only ever shows up around equal values.
Try it: What if you drop PARTITION BY entirely and rank the whole company at once?
Run your query to see results here.
Without a partition, there’s only one window: the whole company. Meera comes out on top company-wide, and the same tie behavior still applies — Sam and Priya still share a rank, just a company-wide one instead of a department-wide one. PARTITION BY is entirely optional; leaving it out just means the window is everything.
HR wants a seniority list: for every department, rank employees by hire_date, earliest hire first (so the longest-tenured person in each department is rank 1). Use RANK, so that anyone who joined on the exact same day would tie. Return name, department, hire_date, and seniority_rank.
Hint 1
Same shape as ranking by salary — just a different column to order by, and the opposite direction (earliest first, not highest first).
Hint 2
PARTITION BY department groups employees into their departments; the ORDER BY inside OVER decides who's #1 within each one.
Hint 3
RANK() OVER (PARTITION BY department ORDER BY hire_date ASC) AS seniority_rank
Solution
Running…
All three ranking functions share the same shape — PARTITION BY to pick the groups, ORDER BY inside OVER to decide the order within each group. They only diverge the moment two rows are genuinely equal, and even then the fix isn’t to avoid ties — it’s to pick the ranking function whose behavior on ties actually matches the question you’re asking.
ROW_NUMBER always hands out unique, sequential numbers, even to exact ties. RANK and DENSE_RANK give ties the same number — RANK then skips ahead by however many rows tied, DENSE_RANK never skips at all.