RANK SQL Guide: Ties, Top N, and Window Examples

What does RANK do in SQL?

SELECT
  category_id,
  product_id,
  revenue,
  RANK() OVER (
    PARTITION BY category_id
    ORDER BY revenue DESC
  ) AS revenue_rank
FROM product_metrics;

If two products share the highest revenue, both receive rank 1 and the next product receives rank 3. That gap is not missing data; it records that two rows occupy earlier positions. Decide whether shared position and gaps match the consumer's definition before choosing RANK.

Define the ranking population, partition, and order

A ranking calculation has three distinct decisions. The query's joins and filters define which rows enter. PARTITION BY defines independent competitions. ORDER BY defines which values earn earlier positions and which rows are peers. Changing any one of these changes the meaning. The output's final display order is separate and should be specified with the outer query's ORDER BY.

Partition keys should reflect a business boundary, such as category, league, region, or reporting period. Over-partitioning can create meaningless one-row contests; under-partitioning compares entities that should not compete. Null partition keys form a group together unless filtered or normalized. Confirm whether an “unknown region” group should be ranked or excluded.

Treat ties as a business rule, not a formatting accident

Rows are tied when every expression in the window ORDER BY compares equally. If the order contains only revenue, equal revenue means a tie. Adding product ID breaks the tie and makes every rank unique, effectively changing RANK into a row sequence. That may be appropriate for deterministic selection, but it no longer answers “which products share the same revenue position?”

Do not break ties solely because row order looks unstable. Preserve the semantic rank and use an outer ordering field for reproducible presentation. Break ties inside the window only when the business policy truly chooses an earlier position.

Compare RANK, DENSE_RANK, and ROW_NUMBER

All three functions use the same partition and ordering framework but encode different questions. ROW_NUMBER assigns a unique sequence and therefore returns exactly one row for any chosen position when the order is deterministic. RANK preserves ties and leaves gaps. DENSE_RANK preserves ties but counts distinct ordering values without gaps.

SELECT
  player_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id) AS row_no,
  RANK() OVER (ORDER BY score DESC) AS rank_no,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_no
FROM scores;

The row-number specification includes a stable key because it must choose a unique sequence. The rank specifications omit that key because equal scores should remain peers. Calculate multiple functions together on a test fixture to make boundary behavior visible before committing to one metric.

Define whether Top N means positions, values, or rows

“Top 3” is ambiguous. Filtering RANK() <= 3 returns all rows occupying the first three positional ranks, so ties may produce more than three rows and may leave fewer than three distinct rank values when a large tie creates a gap. DENSE_RANK() <= 3 returns the top three distinct ordering values. ROW_NUMBER() <= 3 returns exactly three rows per partition when at least three exist.

WITH ranked AS (
  SELECT
    category_id,
    product_id,
    revenue,
    RANK() OVER (
      PARTITION BY category_id
      ORDER BY revenue DESC
    ) AS rnk
  FROM product_metrics
)
SELECT *
FROM ranked
WHERE rnk <= 3;

Document which definition the product uses and test a tie exactly at the boundary. If capacity is fixed—three prizes, ten API results, or five promotion slots—ties need a policy instead of an accidental overflow. If fairness requires all tied entities, downstream consumers must accept variable row counts.

Filter ranking results at the correct query stage

A standard WHERE clause is evaluated before window results are available, so ranking normally needs a subquery or CTE followed by an outer filter. Databases with QUALIFY can express the filter in the same query block, but the logical intent remains “calculate the window over the eligible population, then retain ranked rows.”

A filter placed inside the ranked stage changes the competition. Filtering inactive products before RANK asks “rank active products.” Ranking all products and filtering inactive rows afterward asks “show active products with their rank among all products.” Both are valid and often produce different positions. State the population in words before deciding filter placement.

Control NULL ordering and data types

Database defaults for positioning NULL in ascending or descending order differ. If NULL means “not measured,” allowing it to become a top or bottom rank silently may mislead users. Filter it, map it to a documented category, or specify NULLS FIRST or NULLS LAST where supported. Do not replace NULL with zero unless they mean the same thing.

Ordering expressions also inherit collation, time-zone conversion, numeric precision, and type-casting rules. Ranking rounded display values while ordering unrounded measures can make equal-looking rows receive different ranks. Decide whether ties are based on stored precision, business rounding, or a standardized score and expose that rule.

Rank after establishing the intended row grain

Window functions rank rows presented to them. If a customer is duplicated by an order join, RANK sees several customer rows. If revenue is duplicated by a many-to-many relationship, the ordering measure itself may be inflated. Aggregate or select one row per competing entity before ranking, unless the child rows are the entities that should compete.

After ranking, a later one-to-many join can duplicate each ranked entity without changing the stored rank value, making a rank distribution or row count look wrong. Preserve a stable entity key and measure both rows and distinct ranked entities after every downstream relationship. Keep ranking stages close to the grain they describe.

Review sorting, partition skew, and reusable windows

RANK usually requires rows ordered by partition and ranking keys. Large sorts consume memory and can spill; one enormous partition can dominate runtime even when average partitions are small. Multiple window functions may share sorting work when their partition and order specifications align, but small differences in direction, expression, collation, or null handling can require separate processing.

Reduce input only with semantically safe filters, remove unnecessary wide columns before sorting, and consider indexes or physical organization that support common partition-order access. Then inspect the actual plan. An index that helps one ranking workload can be expensive to maintain and may not eliminate sorting for another direction or partition.

Validate RANK with adversarial tie cases

• Write what one input row and one partition represent.
• Create ties at first place, in the middle, and exactly at the Top-N boundary.
• Test one-row partitions, all-tied partitions, NULL ordering values, and empty populations.
• Compare RANK, DENSE_RANK, and ROW_NUMBER on the same fixture.
• Separate semantic tie rules from outer display ordering.
• Measure rows and distinct competitor keys before and after joins.
• Check maximum partition size, actual sort memory, spills, and repeated windows.
• Preserve the fixture and expected ranks as a regression test.

Diagnose a Top-N result that returns too many rows

Suppose an API promises five products per category but sometimes returns seven. The query filters RANK() <= 5, and three products tie at rank 5. The SQL is behaving correctly; the contract is inconsistent. Product owners must choose between exactly five deterministic rows, all products in the top five positions, or all products across the top five distinct revenue values.

Reproduce the tie, calculate all three ranking functions, and show counts at the boundary. If exactly five rows are mandatory, define a documented secondary policy and use ROW_NUMBER or a tie-broken order. If fairness requires all ties, change the API schema and pagination assumptions to accept variable counts. Save the boundary fixture so later code cannot silently reverse the decision.

RANK SQL 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