Topic 42 of 52
Standard Positions (RANK())
Overview
RANK() assigns a rank to each row within a partition based on the ORDER BY clause. Tied rows receive the same rank, and the next rank is skipped (gaps). Used for leaderboards, competition rankings, and finding top N where ties matter.
Syntax
sql
-- RANK: same rank for ties, gaps after ties
SELECT
name, score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM exam_results;
-- Output:
-- Alice: 95 → rank 1
-- Bob: 92 → rank 2
-- Carol: 92 → rank 2 (tie with Bob!)
-- Dave: 88 → rank 4 (skipped rank 3 due to tie)
-- RANK vs ROW_NUMBER vs DENSE_RANK comparison
SELECT
name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, -- 1,2,3,4 (unique)
RANK() OVER (ORDER BY score DESC) AS rank, -- 1,2,2,4 (gap)
DENSE_RANK() OVER (ORDER BY score DESC) AS dense -- 1,2,2,3 (no gap)
FROM exam_results;
-- Ranked within groups
SELECT
department, name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;Common Pitfalls
- RANK() creates gaps after ties (1, 2, 2, 4) — use DENSE_RANK() if you need gapless ranking (1, 2, 2, 3).
- RANK() ties break arbitrarily among tied rows — add a secondary ORDER BY column for deterministic results.
- Interview tip: 'Find the employee with the 2nd highest salary' = DENSE_RANK() = 2 (handles ties better than OFFSET 1 LIMIT 1).
Real-World Example
Monthly sales leaderboard with proper tie handling:
example
sql
-- Monthly sales competition: tie-aware ranking
SELECT
s.name,
s.team,
SUM(o.amount) AS monthly_revenue,
COUNT(o.id) AS deals_closed,
RANK() OVER (
ORDER BY SUM(o.amount) DESC
) AS overall_rank,
RANK() OVER (
PARTITION BY s.team
ORDER BY SUM(o.amount) DESC
) AS team_rank
FROM orders o
JOIN salespeople s ON o.salesperson_id = s.id
WHERE DATE_TRUNC('month', o.closed_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY s.id, s.name, s.team
ORDER BY overall_rank, team_rank;
-- Find all employees ranked in top 3 of their department
SELECT department, name, salary, dept_rank
FROM (
SELECT department, name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
) r
WHERE dept_rank <= 3;