ROW_NUMBER
The ROW_NUMBER() window function is one of the most commonly used window function. It
returns unique integers for
each row in the resulting order based on some ordering parameter. ROW_NUMBER()
typically has an ORDER BY
specification in the ROW_NUMBER() argument that determines the unique numbers which
start at 1 and increase by 1.
Sample Employee Table
Suppose we have an employees table that we can query the employee_id,
first_name,
last_name, salary. The output looks like below:
SELECT employee_id, first_name, last_name, salary
FROM employees
LIMIT 20;
Find the nth salaried employeed
Now suppose we are interested in knowing the nth largest salary and what employee earns that much. The easiest
approach to finding this is to use ROW_NUMBER() which
will provide a rank. The actual query looks like below:
SELECT
employee_id,
first_name,
last_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;
In this case, notice that we can now use the salary rank to get the salary at a particular position.
Duplicates with ROW_NUMBER()
If you have been observing, you will notice that there are employees that make the same amount by are ranked
different. For example employee_ids $4$ and $46$ both earn the same amount. This is because
ROW_NUMBER() only
does increments of 1 regardless of the evaluation condition. In the coming sessions we will look at alternatives
like RANK() and
DENSE_RANK() which will fix some of these issues. You can use the summary below to
think through these.
|
Function |
Handles Ties |
Sequential Numbers |
|
ROW_NUMBER() |
Assigns different numbers |
Always 1,2,3,4... |
|
RANK() |
Same rank for ties |
May skip numbers (1,2,2,4...) |
|
DENSE_RANK() |
Same rank for ties |
No gaps (1,2,2,3...) |
In the next section, we look at the PARTITION BY window function.