What are Window Functions?
Ranking Functions Comparison
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 3 | 2 | 2 |
| 85 | 4 | 4 | 3 |
4 rows
Calculate across rows while keeping all rows. Unlike GROUP BY, every row stays.
ROW_NUMBER - Unique Number
Ranking Functions Comparison
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 3 | 2 | 2 |
| 85 | 4 | 4 | 3 |
4 rows
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM students;Each row gets unique number: 1, 2, 3, 4...
RANK vs DENSE_RANK
Ranking Functions Comparison
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 3 | 2 | 2 |
| 85 | 4 | 4 | 3 |
4 rows
For ties (same scores):
- RANK: Creates gaps (1, 2, 2, 4)
- DENSE_RANK: No gaps (1, 2, 2, 3)
SELECT name, score,
RANK() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;LAG and LEAD - Look at Other Rows
Ranking Functions Comparison
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 3 | 2 | 2 |
| 85 | 4 | 4 | 3 |
4 rows
SELECT name, score,
LAG(score) OVER (ORDER BY score) AS previous,
LEAD(score) OVER (ORDER BY score) AS next
FROM students;- LAG: Previous row value
- LEAD: Next row value
PARTITION BY - Groups Within Window
Ranking Functions Comparison
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 3 | 2 | 2 |
| 85 | 4 | 4 | 3 |
4 rows
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;Ranking restarts for each department.
Real Example: Top 3 per Category
Ranking Functions Comparison
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 3 | 2 | 2 |
| 85 | 4 | 4 | 3 |
4 rows
SELECT * FROM (
SELECT product, category, sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
FROM products
) WHERE rank <= 3;Summary
Ranking Functions Comparison
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 90 | 3 | 2 | 2 |
| 85 | 4 | 4 | 3 |
4 rows
- ROW_NUMBER: Unique numbers (1, 2, 3, 4)
- RANK: Ties same, gaps after (1, 2, 2, 4)
- DENSE_RANK: Ties same, no gaps (1, 2, 2, 3)
- LAG/LEAD: Previous/next row values
- PARTITION BY: Groups within window