SQL › Ranking Your Rows

ROW_NUMBER, RANK, DENSE_RANK and NTILE

Ranking functions give each row a position in its window. They differ only in what they do with ties, rows that sort equally.

Syntax

Order of execution: FROM → WHERE → GROUP BY → window functions → SELECT → ORDER BY

Code
SELECT column,
       ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) AS rn,
       RANK()       OVER (ORDER BY sort_column DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY sort_column DESC) AS dense,
       NTILE(4)     OVER (ORDER BY sort_column DESC) AS bucket
FROM table_name;

These functions need an ORDER BY inside OVER in SQL Server and Oracle. PostgreSQL and MySQL accept ROW_NUMBER() OVER (), but then the numbers follow no fixed order.

Examples

Three ways to rank

Products in categories 1 and 2 ranked by rating, best first.

Code
SELECT product_name, rating,
       ROW_NUMBER() OVER (ORDER BY rating DESC NULLS LAST, product_id) AS row_num,
       RANK()       OVER (ORDER BY rating DESC NULLS LAST) AS rnk,
       DENSE_RANK() OVER (ORDER BY rating DESC NULLS LAST) AS dense
FROM products
WHERE category_id IN (1, 2)
ORDER BY row_num;

NULLs when sorting high to low

In PostgreSQL and Oracle, DESC puts NULLs first. Add NULLS LAST so unrated products don't come out on top. MySQL and SQL Server already put NULLs last with DESC, and have no NULLS LAST.

Code
SELECT product_name, rating FROM products ORDER BY rating DESC LIMIT 3;             -- unrated first
SELECT product_name, rating FROM products ORDER BY rating DESC NULLS LAST LIMIT 3;  -- best rated first

The latest row per group

Number each customer's orders from newest to oldest, then keep number 1.

Code
SELECT customer_id, order_id, order_date
FROM (SELECT customer_id, order_id, order_date,
             ROW_NUMBER() OVER (PARTITION BY customer_id
                                ORDER BY order_date DESC, order_id DESC) AS rn
      FROM orders) t
WHERE rn = 1
ORDER BY customer_id;
 
-- Fails: the number doesn't exist yet when WHERE runs
SELECT order_id, ROW_NUMBER() OVER (ORDER BY order_date) AS rn FROM orders WHERE rn = 1;

The Nth highest value

For the Nth highest value, keep DENSE_RANK() = N: equal values share a number, and no number is skipped. With RANK, a tie for first leaves no rank 2, and ROW_NUMBER gives tied rows different numbers.

Code
-- The products with the second-highest price in the shop; equal prices count once
SELECT product_name, price
FROM (SELECT product_name, price, DENSE_RANK() OVER (ORDER BY price DESC) AS price_rank
      FROM products) t
WHERE price_rank = 2;

Without a window function, SELECT MAX(price) FROM products WHERE price < (SELECT MAX(price) FROM products) finds the second-highest price too, but it gets long for the third or fourth.

Buckets with NTILE

Split the Toys and Books products into three price bands.

Code
SELECT product_name, price, NTILE(3) OVER (ORDER BY price DESC, product_id) AS band
FROM products
WHERE category_id IN (2, 8)
ORDER BY band, price DESC, product_id;

Percentages: PERCENT_RANK and CUME_DIST

PERCENT_RANK is the share of the other rows that sort before this one: 0.75 means three quarters of the other products are cheaper. CUME_DIST is the share of all rows that sort up to this one, ties included, so CUME_DIST() <= 0.3 keeps the first 30%.

Both return a floating-point number. PostgreSQL's ROUND with decimal places needs a NUMERIC, so cast first.

Code
-- Where each Books and Toys product's price stands, cheapest first
SELECT product_name, price,
       ROUND(CAST(PERCENT_RANK() OVER (ORDER BY price) AS NUMERIC), 2) AS pct_rank,
       ROUND(CAST(CUME_DIST() OVER (ORDER BY price) AS NUMERIC), 2) AS cume_dist
FROM products
WHERE category_id IN (2, 8)
ORDER BY price, product_name;

Remember

• ROW_NUMBER never ties; give it a tie-breaker so the numbering is the same every time.
• RANK leaves gaps after ties; DENSE_RANK doesn't.
• In PostgreSQL, DESC sorts NULLs first; use NULLS LAST when NULL means "no value".
• To keep the top rows, rank in a subquery or CTE and filter outside it.
• The Nth highest value is DENSE_RANK() = N; RANK and ROW_NUMBER count ties differently.
• NTILE buckets differ in size by at most one, and the earlier buckets get the extra rows.

Practice

1. Price Ranks With Ties (Medium)
2. Top Two Products per Category (Medium)
3. Customer Spend Quartiles (Medium)
4. Third-Highest Order in Each City (Hard)