ROW_NUMBER, RANK, DENSE_RANK and NTILE

Ranking window functions number rows in order: ROW_NUMBER gives every row a unique number, RANK and DENSE_RANK give ties the same rank, and NTILE splits rows into equal buckets. They solve some of the most common interview questions: top N per group, nth highest value and removing duplicates.

Syntax

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
RANK()       OVER (PARTITION BY ... ORDER BY ...)
DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)
NTILE(n)     OVER (ORDER BY ...)

ORDER BY inside OVER is required: it defines what "first" means.

How they handle ties

ShopKart's Technology team has two people on the same salary. Watch how each function treats them:

SELECT employee_name,
       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
WHERE department = 'Technology';

Result:

(4 rows)

Examples

1. Rank within each group

Products ranked by price inside each category:

SELECT category_id,
       product_name,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
FROM products
WHERE category_id IN (1, 2, 3)
ORDER BY category_id, price_rank;

Result:

(9 rows)

PARTITION BY category_id restarts the numbering for each category.

2. Top N per group

The best-selling product (by units) in each category. Rank in a CTE, filter outside:

WITH product_units AS (
  SELECT p.category_id,
         p.product_name,
         SUM(oi.quantity) AS units
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category_id, p.product_name
),
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY units DESC, product_name) AS rn
  FROM product_units
)
SELECT category_id, product_name, units
FROM ranked
WHERE rn = 1
ORDER BY category_id;

Result:

(6 rows)

Change rn = 1 to rn <= 3 for the top 3. Adding product_name to the ORDER BY makes ties deterministic.

3. Nth highest value

The second-highest salary at ShopKart:

WITH ranked AS (
  SELECT employee_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT employee_name, salary
FROM ranked
WHERE rnk = 2;

Result:

(1 row)

Use DENSE_RANK here: if two people shared the top salary, RANK would have no rank 2 at all.

4. Each customer's latest order

ROW_NUMBER is the go-to for "most recent record per entity":

WITH ordered AS (
  SELECT customer_id, order_id, order_date, status,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date, status
FROM ordered
WHERE rn = 1
ORDER BY customer_id
LIMIT 6;

Result:

(6 rows)

The same pattern removes duplicate rows: number them within each duplicate group and keep rn = 1.

5. NTILE: split into buckets

Divide products into 4 price quartiles:

SELECT product_name,
       price,
       NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products
ORDER BY price DESC
LIMIT 8;

Result:

(8 rows)

With 15 products and 4 buckets, the first three buckets get 4 products and the last gets 3. NTILE is useful for customer segments like "top 25% of spenders".

Common mistakes

• Filtering on the rank in the same query. WHERE rn = 1 fails next to the window function; use a CTE.
• ROW_NUMBER for "top N with ties". If ties matter, use RANK or DENSE_RANK so tied rows aren't arbitrarily dropped.
• No tie-breaker. ROW_NUMBER() OVER (ORDER BY salary) can number tied rows differently between runs. Add a unique column to the ORDER BY.

Practice question

Find the highest-paid employee in each department.

WITH ranked AS (
  SELECT employee_name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, employee_name, salary
FROM ranked
WHERE rnk = 1
ORDER BY department;

Key takeaways

• ROW_NUMBER = unique numbers; RANK = ties share, then gaps; DENSE_RANK = ties share, no gaps.
• PARTITION BY ranks within groups; filter the rank in an outer query.
• Top N per group: rank in a CTE, then WHERE rank <= N.
• Nth highest: DENSE_RANK() ... WHERE rnk = N.