CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 3 of 5 · 1 min read

RANK, DENSE_RANK and ROW_NUMBER

Three ways to number rows within a window, differing only in how they handle ties.

Think of a race that ends in a tie for second place. One scoring method gives both runners second place and skips third entirely. Another gives both second place and lets third follow immediately. A third method just assigns positions in listed order, breaking ties arbitrarily.

In plain words: ROW_NUMBER assigns a unique, increasing number to every row, with no regard for ties. RANK assigns the same rank to tied rows, then skips the next rank number. DENSE_RANK assigns the same rank to tied rows too, but does not skip the next number.

A worked example. Ranking books by price, where two books tie for the most expensive.

The key lines.

SELECT title, price,
       RANK() OVER (ORDER BY price DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
FROM books;

RANK, DENSE_RANK, ROW_NUMBER

ROW_NUMBER gives every row a unique number. RANK gives tied rows the same rank, then skips ahead. DENSE_RANK gives tied rows the same rank, with no gap afterward.

Example: two books tied for first place both get rank 1 under RANK and DENSE_RANK, but the next book gets rank 3 under RANK and rank 2 under DENSE_RANK.