RANK and DENSE_RANK

Overview

Both RANK() and DENSE_RANK() assign an integer rank to each row within a partition based on an ORDER BY expression, and both give tied rows the same rank. They differ in what happens to the rank sequence after a tie:

• RANK() leaves a gap: if two rows tie for rank 1, the next row gets rank 3.
• DENSE_RANK() leaves no gap: if two rows tie for rank 1, the next row gets rank 2.

Copy code
SELECT
  student,
  score,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM exam_results;

Given scores 95, 95, 90, 80, RANK() produces 1, 1, 3, 4 while DENSE_RANK() produces 1, 1, 2, 3.

When to use which

• Use RANK() for leaderboard-style results where the gap reflects "how many people beat you."
• Use DENSE_RANK() when you want a compact set of tiers or buckets, such as labeling "top 3 distinct price points" regardless of how many products share each price.

Copy code
SELECT * EXCLUDE (dense_rnk)
FROM (
  SELECT product, price, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
  FROM products
)
WHERE dense_rnk <= 3;

DuckDB specifics

DuckDB implements both functions per the SQL standard, requiring an ORDER BY inside the OVER clause (a PARTITION BY is optional). As with other window functions, DuckDB's QUALIFY clause lets you filter directly on the rank without a subquery:

Copy code
SELECT product, price, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
FROM products
QUALIFY dense_rnk <= 3;

For a tie-free row count instead, use ROW_NUMBER(); for ranks expressed as a fraction between 0 and 1, use PERCENT_RANK() or CUME_DIST().