DocsWindow FunctionsRANK & DENSE_RANK

RANK & DENSE_RANK

Window Functions

Concept

RANK() and DENSE_RANK() assign rank numbers to rows within a partition, but unlike ROW_NUMBER they handle ties. When two rows have equal ORDER BY values, RANK gives them the same rank number and then skips the next rank(s). DENSE_RANK also gives tied rows the same number but does not skip — the next distinct value gets the very next integer.

For example, if three products are tied for 2nd place: RANK assigns 2, 2, 2, 5 (skipping 3 and 4), while DENSE_RANK assigns 2, 2, 2, 3 (no gaps). Choose RANK when you want traditional competition-style ranking and DENSE_RANK when you want contiguous rank numbers regardless of ties.

These functions are commonly used in leaderboards, grading systems, and percentile calculations. Like ROW_NUMBER, they require an OVER clause with ORDER BY and an optional PARTITION BY. They cannot be filtered directly in WHERE — use a subquery or CTE.

Syntax

-- RANK (gaps after ties)
SELECT
  name,
  price,
  RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- DENSE_RANK (no gaps)
SELECT
  name,
  price,
  DENSE_RANK() OVER (ORDER BY price DESC) AS price_drank
FROM products;

-- Both for comparison
SELECT
  name, price,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Rank customers by total spending (with ties)
SELECT
  c.first_name || ' ' || c.last_name AS customer,
  SUM(o.total_amount) AS total_spent,
  RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spend_rank,
  DENSE_RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spend_dense_rank
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY spend_rank;

-- Top-ranked product per category using DENSE_RANK
SELECT category, name, price
FROM (
  SELECT
    category, name, price,
    DENSE_RANK() OVER (
      PARTITION BY category ORDER BY price DESC
    ) AS dr
  FROM products
) ranked
WHERE dr = 1;

Common Pitfalls & Tips

1RANK produces gaps after ties (1, 2, 2, 4). If contiguous numbering matters for your use case, use DENSE_RANK instead.
2DENSE_RANK with many tied values can make the maximum rank misleadingly low. Verify whether your business logic needs RANK or DENSE_RANK semantics.
3Like ROW_NUMBER, RANK and DENSE_RANK cannot be filtered in WHERE. Wrap in a subquery or CTE to filter by rank value.

Practice RANK & DENSE_RANK queries