RANK and DENSE_RANK

Learn the ranking functions.

RANK and DENSE_RANK

RANK skips on ties, DENSE_RANK does not. Both give rankings.

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM products;

This query gives two kinds of ranking.

name, price, RANK() OVER (ORDER BY price DESC) AS rk, … is selected from products. RANK() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.

RANK and DENSE_RANK — details

If two rows tie at 1, RANK skips to 3, DENSE_RANK continues at 2.

In competition results, use RANK if you want "two people first, the next one third", and DENSE_RANK if you want "the next one second".

Exercise RANK and DENSE_RANK

Compute both RANK() and DENSE_RANK() for each product.

Show example solution

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM products;

Test yourself

RANK and DENSE_RANK: match the terms

RANK

skips
DENSE_RANK

no skip
ties

same value
ranking

rank

Add DENSE_RANK

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk,
  ____() OVER (ORDER BY price DESC) AS dr
FROM products;

Show answer

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM products;

What does DENSE_RANK do on ties?
- Skips
- An error
- Does not skip
- Resets
Show answer

Does not skip — DENSE_RANK does not skip on ties.

Next lesson: LAG and LEAD

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies