DENSE_RANK SQL: Ties, Top N, and Continuous Ranking

What does DENSE_RANK do in SQL?

SELECT category_id, product_id, price,
  DENSE_RANK() OVER (
    PARTITION BY category_id
    ORDER BY price DESC
  ) AS price_level
FROM products;

If prices are 100, 100, 80, and 60, dense ranks are 1, 1, 2, and 3. This encodes the ordinal position of each distinct price, not the physical row position. Use it when continuous value levels have business meaning.

Think in ordered distinct values, then attach rows

A reliable model has two steps. First, take the distinct window ordering tuples within a partition and sort them. Second, assign consecutive positions and attach every row sharing each tuple. This explains why ties share a dense rank and why no gap appears afterward. It also explains why adding another ordering expression can split a level.

The input population still comes from joins and filters. If rows are duplicated before the window, DENSE_RANK preserves those duplicates. If the ranking measure is inflated before calculation, the level itself is wrong. Establish one row per competing entity first unless child rows are intended competitors.

Choose DENSE_RANK, RANK, or ROW_NUMBER by the boundary

These are different contracts, not interchangeable syntax. A leaderboard often wants RANK because two first-place competitors occupy two positions. A product catalog may want DENSE_RANK to label the first three distinct price bands. A fixed-capacity widget often wants ROW_NUMBER with a documented tie-break rule.

Define peers with the complete ordering tuple

Rows are peers only when every expression in the window ORDER BY compares equally. Adding product ID makes each tuple unique and removes ties, defeating dense value levels. Use an outer ORDER BY for stable display inside a tie without changing the window's semantic peer definition.

If business rules genuinely break ties—earlier completion, higher quality, lower risk—include those rules and explain that the dense rank now represents a composite score. If the rules are unavailable, do not invent an arbitrary order merely for reproducibility.

Use DENSE_RANK for Top N distinct ordering values

WITH scored AS (
  SELECT group_id, item_id, score,
    DENSE_RANK() OVER (
      PARTITION BY group_id
      ORDER BY score DESC
    ) AS level_no
  FROM item_scores
)
SELECT * FROM scored
WHERE level_no <= 3;

This can return more than three rows because every tie at each of the top three distinct scores qualifies. That is correct only if the consumer asks for value levels. Test a large tie at the boundary and make pagination or API limits accept variable counts.

If a fixed number of entities is required, use ROW_NUMBER with a policy. If positional competition is required, use RANK. Name the output “dense score level” or “distinct price rank” instead of a vague “rank.”

Decide which rows enter before ranking

Filters inside the ranked stage define the competition. Ranking active products answers a different question from ranking all products and showing only active ones afterward. Standard SQL requires an outer query or CTE to filter window results; QUALIFY can shorten syntax where supported without changing logical order.

Document eligibility, partition, ordering value, peer rule, and boundary together. A future reviewer should not need to infer why records disappeared before ranking or after ranking.

Make NULL and precision part of the level definition

Database defaults for NULL ordering differ. If NULL means unmeasured, letting it form a top or bottom dense level can mislead. Filter it, label it separately, or specify null order where supported. Replacing NULL with zero is valid only when absence and zero are equivalent.

Ties also depend on precision. Values displayed as 9.5 may differ internally and receive different levels. Decide whether levels use raw values, rounded business values, or normalized score bands. If rounding defines peers, apply it explicitly before DENSE_RANK and test boundary values.

Apply dense levels to value bands and categorical tiers

DENSE_RANK works well for distinct price levels, salary bands derived from actual values, score tiers, version levels, and category-specific top values. It can also compress sparse numeric values into consecutive ordinal labels. That label describes order, not distance: levels 1 and 2 may differ by one unit or one million.

If distance matters, keep the original value and calculate differences separately. If stable predefined categories matter, use a dimension table rather than letting observed values redefine levels each load.

Review ordering cost and partition skew

DENSE_RANK generally requires ordered partitions. Large inputs can sort, spill, or create long single-partition work. Multiple windows can reuse processing when complete partition and order definitions align. Remove unnecessary wide columns before sorting, apply only semantically safe filters, and inspect actual memory, rows, spills, and worker balance.

Validate continuous ranks with explicit distinct values

• Test one row, all ties, no ties, NULL, and rounded-equal values.
• List distinct ordering tuples and expected consecutive positions manually.
• Compare RANK, DENSE_RANK, and ROW_NUMBER on one fixture.
• Place a large tie at the Top-N boundary.
• Measure rows and distinct competitor keys around joins.
• Inspect maximum partition size, sort memory, spills, and reused windows.
• Preserve expected levels in regression tests.

Diagnose a tier count that changes after rounding

Suppose a dashboard displays two products at 9.5 but assigns them dense levels 2 and 3. The ranking uses raw values 9.46 and 9.54 while presentation rounds to one decimal. Decide whether the metric ranks raw quality or displayed score bands. If displayed bands define peers, rank the documented rounded expression and test values on both sides of every boundary.

DENSE_RANK frequently asked questions

Continue with a related guide

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents.

• SQL window functions
• PARTITION BY in SQL
• ROW_NUMBER in SQL

Govern dense levels as a changing data product

Observed dense levels are relative to the current population. If the highest score disappears, every lower score can move up even though no remaining entity changed. New intermediate values can shift later levels. That behavior is correct for a live ranking but unsuitable when level identifiers must remain stable across reports, contracts, or historical comparisons. In those cases, use a maintained band dimension or preserve the original ranking snapshot instead of recomputing from today's population.

Partition membership creates similar movement. Reassigning a product to another category changes its comparison set and may shift levels in both categories. Record effective dates for category membership when historical reports must reproduce the level visible at that time. Decide whether backdated corrections rewrite history or create a revised version. DENSE_RANK alone does not provide temporal governance.

Monitor the number of distinct levels, rows per level, largest tie group, null-level population, and shifts between snapshots. A sudden collapse from hundreds of levels to a few can indicate rounding, type conversion, default substitution, or upstream truncation. A sudden explosion can indicate precision changes or duplicated partition keys. Compare both raw ordering values and assigned levels during incidents.

When publishing a Top-K dense result, return the original value, dense level, partition key, and an indicator that row count can exceed K. Consumers can then explain why a request for three levels produced eight entities. Include a boundary fixture with a large tie and a snapshot-shift fixture in automated tests. This protects both mathematical correctness and product expectations.

Decide whether to calculate or store dense levels

Calculate levels at query time when the population is small, current relative position is required, and freshness outweighs repeatability. Materialize them when many consumers need the same expensive partitioned sort or when a reporting cutoff must be reproducible. A stored result needs source cutoff, calculation version, partition definition, ordering expression, and refresh status. Without that metadata, two tables can both contain a “dense rank” while representing different populations or business rules.

For incremental refreshes, remember that one inserted distinct value can shift many later levels. Updating only the new entity is incorrect. Recompute the affected partition or maintain an ordered-level structure with tested change propagation. Compare a full recomputation periodically to detect drift.

Keep the level contract explicit

Before release, confirm that consumers want consecutive distinct-value levels, accept every tie, and understand that level membership can change with the population. Record those decisions beside the query.