SQL

Find the Nth Largest Value Using ROW_NUMBER Window Function

Discover how to rank rows within partitions and find specific ranked items (like the Nth largest salary) using the powerful `ROW_NUMBER()` window function.

SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT
        employee_id,
        first_name,
        last_name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
    FROM
        employees
) AS ranked_employees
WHERE rn = 3; -- To find the 3rd highest salary

How it works: This snippet utilizes a subquery with the `ROW_NUMBER()` window function to assign a unique rank to each row based on a specified order (here, `salary` in descending order). The outer query then filters for the row with a specific rank (e.g., `rn = 3`), effectively finding the Nth largest value (in this case, the 3rd highest salary). This is a common pattern for top-N queries or more advanced data ranking scenarios.

Find the Nth Largest Value Using ROW_NUMBER Window Function

Need help integrating this into your project?