Window functions in SQL are powerful tools for performing calculations across a set of rows that are related to the current row. The functions ROW_NUMBER(), RANK(), and DENSE_RANK() are commonly used to assign ranks based on specific criteria. Here’s how you can use them, along with examples.
Overview of Window Functions
1. ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition of a result set. If two rows have the same values in the ordering columns, they will still receive different row numbers (i.e., there are no ties).
2. RANK(): Assigns a rank to each row within a partition of a result set, with gaps in the ranking for ties. If two rows have the same value and get the same rank, the next rank will be incremented by the number of ties.
3. DENSE_RANK(): Similar to RANK(), but without gaps in the ranking for ties. If two rows receive the same rank, the next rank will continue sequentially without skipping.
Using these Functions
To use these functions, you typically need a SELECT statement along with an OVER clause that specifies the partitioning and ordering of the data.
Here’s a general syntax:
SELECT
column1,
column2,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num,
RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank,
DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS dense_rank
FROM
your_table;
Example
Let's say you have a table called Employees with the following columns: EmployeeID, Department, and Salary.
Here’s how you might assign ranks to employees based on their salary within each department.
Sample Data
| EmployeeID | Department | Salary |
|------------|------------|--------|
| 1 | HR | 50000 |
| 2 | HR | 60000 |
| 3 | HR | 60000 |
| 4 | IT | 80000 |
| 5 | IT | 90000 |
| 6 | IT | 85000 |
SQL Statement
SELECT
EmployeeID,
Department,
Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS row_num,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS dense_rank
FROM
Employees;
Result
| EmployeeID | Department | Salary | row_num | rank | dense_rank |
|------------|------------|--------|---------|------|------------|
| 2 | HR | 60000 | 1 | 1 | 1 |
| 3 | HR | 60000 | 2 | 1 | 1 |
| 1 | HR | 50000 | 3 | 2 | 2 |
| 5 | IT | 90000 | 1 | 1 | 1 |
| 4 | IT | 80000 | 2 | 2 | 2 |
| 6 | IT | 85000 | 3 | 2 | 2 |
Explanation of the Results
- ROW_NUMBER() assigns a unique row number to each employee within their department based on their salary. Rows with the same salary are given different row numbers.
- RANK() assigns the same rank to employees with the same salary (both at 60000 in HR), followed by a skip in the ranking (the next rank is 2).
- DENSE_RANK() also assigns the same rank to employees at the same salary but does not skip ranks (the next rank after 1 is also 2).
Key Points
- Choose your ranking function based on how you want to handle ties—ROW_NUMBER() for distinct ranks, RANK() for gaps, and DENSE_RANK() for a continuous ranking without gaps.
- Use the PARTITION BY clause to divide the result set into subsets.
- Use ORDER BY to specify how the rows are ordered within each partition for ranking.