ROW_NUMBER SQL: Ranking, Deduplication, and Top N

What does ROW_NUMBER do in SQL?

The function does not permanently number table rows and does not guarantee final display order. It calculates a value for the current result set. Use it when you need one winner per business key, the first few rows per group, numbered slices for a report, or an explicit row sequence for downstream logic.

ROW_NUMBER() OVER (
  PARTITION BY group_column
  ORDER BY sort_column DESC, unique_id
) AS row_num

Understand the ROW_NUMBER syntax before using it

Four decisions control the result. The input rowset decides what can be numbered. PARTITION BY decides where numbering restarts. The window ORDER BY decides which row comes first. A tie-breaker decides whether that order is reproducible. Leaving any decision implicit can produce a query that runs successfully but answers the wrong question.

Make ROW_NUMBER deterministic when ties exist

Suppose two status updates for the same ticket share the same updated_at value. If the window orders only by that timestamp, either update may receive row number 1. The result can change after a new execution plan, parallel processing, a maintenance operation, or simply a different physical row order. The SQL is valid, but the winner is not defined.

-- Unstable when updated_at contains ties
ROW_NUMBER() OVER (
  PARTITION BY ticket_id
  ORDER BY updated_at DESC
)

-- Stable if event_id is unique and immutable
ROW_NUMBER() OVER (
  PARTITION BY ticket_id
  ORDER BY updated_at DESC, event_id DESC
)

A good tie-breaker is non-null, stable, and unique within the partition. A primary key is often suitable. Do not append a random function merely to silence ties; that explicitly makes selection variable. Also remember that the window ordering controls numbering, not final presentation. Add a query-level ORDER BY when consumers need the output itself sorted.

ROW_NUMBER vs RANK vs DENSE_RANK

Choose the function according to the meaning of a tie. For values 100, 90, 90, and 80 ordered descending, ROW_NUMBER produces 1, 2, 3, 4; RANK produces 1, 2, 2, 4; and DENSE_RANK produces 1, 2, 2, 3. The functions are not interchangeable when a boundary such as “top three” is involved.

The PostgreSQL reference defines ROW_NUMBER as the current row's number within its partition and distinguishes it from peer-aware ranking. Use ROW_NUMBER when the requirement is exactly N physical rows or one selected row. Use a peer-aware function when equal business values must share rank.

A complete ROW_NUMBER SQL example

Assume orders contains one row per order. The report needs each customer's orders from newest to oldest and must behave predictably when two orders share a timestamp. The combination of order_date and unique order_id defines the sequence.

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC, order_id DESC
  ) AS customer_order_number
FROM orders
WHERE order_status = 'completed'
ORDER BY customer_id, customer_order_number;

The WHERE clause removes non-completed orders before numbering, so the sequence describes completed orders only. PARTITION BY customer_id restarts at 1 for each customer. The two ordering columns make the order total. Finally, the outer ORDER BY makes the delivered result readable; it does not change the row numbers already calculated.

Return the top N rows per group

A global LIMIT returns N rows for the entire result. To return exactly N rows for every customer, product, or region, calculate a partitioned row number first and filter it outside. This is one of the clearest reasons to use a window function.

WITH ranked_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC, order_id
    ) AS rn
  FROM orders o
  WHERE order_status = 'completed'
)
SELECT *
FROM ranked_orders
WHERE rn <= 3;

This returns a maximum of three rows per customer. It deliberately breaks equal amounts using order_id. If the requirement says “include every order tied for third-highest amount,” use RANK or DENSE_RANK and accept that a customer may return more than three rows. Document that distinction because both interpretations are plausible.

Deduplicate rows and keep the intended winner

“Remove duplicates” is incomplete as a requirement. You must define the duplicate key and the winner. For a customer profile history table, the key might be customer_id; the winner might be the greatest updated_at, followed by the greatest ingestion identifier when timestamps tie. ROW_NUMBER expresses that policy directly.

WITH candidates AS (
  SELECT
    p.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC, ingestion_id DESC
    ) AS rn
  FROM customer_profile_history p
)
SELECT *
FROM candidates
WHERE rn = 1;

Do not use this pattern to hide unexplained duplication introduced by an incorrect join. First measure the row count and key uniqueness before and after each join. If one order becomes five rows because it matches five line items, ranking one row does not repair the grain—it merely discards information according to a possibly unrelated order.

• Define the duplicate key independently of the winner rule.
• Make the winner rule deterministic and explain why it matches the business.
• Count partitions with more than one candidate before discarding rows.
• Retain rejected records or audit counts when deletion has governance impact.

Use ROW_NUMBER for pagination with clear tradeoffs

Numbered pagination can return a requested range and is useful for reports that must jump directly to a page. The ordering must be stable, and the snapshot must be consistent if users expect page boundaries not to move while data changes.

WITH numbered AS (
  SELECT
    order_id,
    order_date,
    customer_id,
    amount,
    ROW_NUMBER() OVER (
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
)
SELECT *
FROM numbered
WHERE rn BETWEEN 101 AND 125
ORDER BY rn;

The apparent simplicity can hide cost: the engine may sort and number every qualifying row before it returns a small page. For deep, frequently requested pages on large changing tables, keyset pagination often performs more predictably by requesting rows after the last seen ordering key. Keyset pagination cannot jump to an arbitrary page as naturally, so the choice depends on product behavior, not syntax preference.

Place filters and joins at the correct grain

The same ROW_NUMBER expression can answer different questions depending on filter placement. Filtering to completed orders before numbering means “latest completed order.” Numbering all orders and filtering status afterward means “latest order, but return it only if completed.” Those results diverge whenever a customer's newest order is pending.

1. State the decision. Write whether eligibility is determined before ranking or whether rank is assigned across the full population.
2. Establish the row grain. Confirm what one row represents before any one-to-many join.
3. Apply eligibility filters. Put filters inside or outside the ranked CTE according to the stated decision.
4. Join attributes deliberately. If possible, rank to one row per key before joining to dimensions that should not multiply rows.
5. Recount and test. Validate row counts, distinct keys, ties, null keys, and missing relationships.

Recognize when ROW_NUMBER adds query complexity

ROW_NUMBER itself is compact. Complexity comes from the work needed to produce the correct ordered partitions and from the layers built around the function. Large sorts, skewed partitions, repeated windows, wide rows, nested CTEs, and joins that expand the input can make a short-looking expression expensive or difficult to verify.

Indexes that begin with selective filter columns and continue with partition and ordering columns may reduce work in some databases and plans, but no universal index recipe exists. Inspect the execution plan with realistic data. Check sort operations, memory spills, estimated versus actual rows, partition skew, and whether an early filter or narrower projection changes the plan safely.

Check ROW_NUMBER behavior across SQL dialects

PostgreSQL, SQL Server, MySQL 8+, Oracle, Snowflake, BigQuery, and other modern platforms support ROW_NUMBER, but surrounding syntax and optimizer behavior differ. SQL Server requires an ORDER BY in the window specification. MySQL documents that omitting it makes numbering nondeterministic. Some cloud warehouses support QUALIFY, which filters window results without an extra visible CTE; PostgreSQL, SQL Server, and MySQL commonly use a subquery or CTE instead.

For authoritative syntax, consult the Microsoft SQL Server ROW_NUMBER documentation and the MySQL window-function reference. Test the exact production engine and version rather than assuming a portable query has identical performance everywhere.

Validate a ROW_NUMBER query before production

Correct sample output is not enough. A ranking query should survive adversarial data, changes in volume, and a second reviewer who can reconstruct the business rule from the SQL.

1. Name the grain. Write what one input row and one output row represent.
2. State eligibility. Decide which filters apply before ranking and which conditions apply after it.
3. Test partitions. Include empty relationships, one-row groups, large groups, null partition keys, and skewed tenants.
4. Force ties. Create duplicate primary sort values and confirm the unique tie-breaker selects the intended row.
5. Check boundaries. Test N-1, N, and N+1 candidates for top-N logic and the first/last rows of every page.
6. Audit joins. Compare total rows and distinct business keys before and after each join.
7. Inspect the plan. Use representative volume to review sorts, spills, row estimates, and repeated window work.
8. Review the complete SQL. Evaluate the function together with CTEs, joins, projections, filters, and downstream assumptions.

ROW_NUMBER 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
• RANK in SQL