DENSE_RANK

Syntax

DENSE_RANK() OVER (
  [PARTITION BY partition_expression, ...]
  ORDER BY sort_expression [ASC | DESC], ...
)

What it does

DENSE_RANK() gives tied rows the same rank, then continues with the next integer without skipping.

Example: values 10, 10, 20 become ranks 1, 1, 2.

Example

SELECT
  product_id,
  category_id,
  sales,
  DENSE_RANK() OVER (
    PARTITION BY category_id
    ORDER BY sales DESC
  ) AS sales_tier
FROM product_sales;

Difference from RANK() and ROW_NUMBER()

• ROW_NUMBER() gives every row a unique number
• RANK() gives ties the same rank and then skips numbers (1, 1, 3)
• DENSE_RANK() gives ties the same rank with no gaps (1, 1, 2)

Use DENSE_RANK() when you want "top N distinct values" rather than "top N rows".

Safety questions

• do you want distinct value tiers, or a strict per-row ordering?
• is the ORDER BY stable enough for the ranking to be reproducible?
• should ties share a tier, or be broken with a secondary sort column?