Ranking Functions

Window functions are one of the most powerful features in SQL. Unlike regular aggregate functions that collapse rows into a single result, window functions perform calculations across a set of rows related to the current row while keeping every individual row in your output.

Ranking functions are the most intuitive place to start. They assign a position number to each row based on some ordering criteria.

The OVER() Clause

Every window function uses the OVER() clause. This is what makes it a "window" function -- it defines the window of rows the function looks at.

-- Basic syntax
function_name() OVER (
  [PARTITION BY column_list]
  [ORDER BY column_list]
)

• PARTITION BY divides rows into groups (like GROUP BY, but without collapsing them)
• ORDER BY determines the order within each partition

ROW_NUMBER()

ROW_NUMBER() assigns a unique, sequential integer to each row within its partition. Even if two rows have identical values, they get different numbers.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

ROW_NUMBER with PARTITION BY

When you add PARTITION BY, the numbering restarts for each group:

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

RANK()

RANK() is similar to ROW_NUMBER(), but it handles ties differently. When two rows have the same value, they receive the same rank, and the next rank skips numbers.

SELECT
  name,
  department,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

If two employees had the same salary of 72000, they would both get rank 4, and the next employee would get rank 6 (rank 5 is skipped).

DENSE_RANK()

DENSE_RANK() also handles ties by assigning the same rank, but it does not skip numbers. The next distinct value always gets the very next integer.

SELECT
  name,
  department,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Comparing All Three

Here is a clear comparison showing how each function handles the same data:

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY salary DESC;

The key differences appear when there are ties:

NTILE()

NTILE(n) divides the ordered rows into n roughly equal groups and assigns a bucket number (1 through n) to each row. This is perfect for creating percentiles or quartiles.

-- Divide employees into salary quartiles
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Quartile 1 contains the highest earners, quartile 4 the lowest. This is useful for segmenting data into equal-sized groups.

Combining NTILE with PARTITION BY

You can create groups within groups:

-- Divide employees into halves within each department
SELECT
  name,
  department,
  salary,
  NTILE(2) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_half
FROM employees;

Practical Use Cases

Finding the Top N per Group

A classic pattern is using ranking functions in a subquery to filter:

-- Top 2 highest-paid employees per department
SELECT name, department, salary
FROM (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

Identifying Duplicates

ROW_NUMBER() can help find and handle duplicate records:

-- If there were duplicate names, find them
SELECT name, department, salary
FROM (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY name
      ORDER BY salary DESC
    ) AS rn
  FROM employees
) sub
WHERE rn > 1;

Try It Yourself

1. Rank all employees by salary within their department using DENSE_RANK(). Which employee is ranked first in each department?

2. Use NTILE(3) to divide all employees into three salary tiers. How many employees are in each tier?

3. Use ROW_NUMBER() with PARTITION BY department to number employees within each department by hire date. Who was hired first in each department?