DENSE_RANK

Syntax

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

Parameter	Type	Notes
PARTITION BY	expression, …	Optional. Restarts the ranking for each group. Without it the whole result set is one partition.
ORDER BY*	expression [ASC\|DESC], …	Defines the ranking order. Rows that are equal under this ordering are a tie and share a rank.

DENSE_RANK() itself takes no arguments — the OVER (…) window clause does all the work.

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.

Compare the three ranking functions

Same input (90000, 90000, 80000, 70000), three different outputs:

salary	ROW_NUMBER	RANK	DENSE_RANK
90,000	1	1	1
90,000	2	1	1
80,000	3	3 ← gap	2 ← no gap
70,000	4	4	3

ROW_NUMBER — every row unique; ties broken arbitrarily unless you add a tiebreaker.
RANK — ties share a rank, then skips ahead by the tie count.
DENSE_RANK — ties share a rank, next rank is always +1 — no gaps.

Reach for DENSE_RANK when you mean "top 3 distinct value tiers"; reach for ROW_NUMBER when you mean "exactly 3 rows."

Example

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

SQL playground

Loading editor…

⌘/Ctrl + Enter

Category 1 has two products tied at 400. DENSE_RANK gives the next product tier 2; RANK gives it 3.

Filtering by rank

You can't put DENSE_RANK() directly in WHERE:

SELECT * FROM product_sales
WHERE DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) <= 2;
-- ERROR: window functions are not allowed in WHERE

WHERE runs before window functions are computed — there's no rank yet to filter on. Wrap the query in a subquery or CTE and filter the outer layer:

SQL playground

Loading editor…

⌘/Ctrl + Enter

(Postgres, MySQL 8+, SQLite, DuckDB: this is what QUALIFY exists to skip in Snowflake/BigQuery/Databricks — ... QUALIFY DENSE_RANK() OVER (...) <= 2.)

RANK — same ties, but skips numbers afterwards (1, 1, 3).
ROW_NUMBER — always unique; ties are broken arbitrarily unless you add a tiebreaker.
ORDER BY — the ordering the window sees; make it deterministic for reproducible ranks.

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?