SQL ROW_NUMBER(): How to Number Rows

By Sharon Ben-Moshe · August 26, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Learn SQL ROW_NUMBER with practical examples for numbering rows, latest-row-per-group queries, tie-breaking, and when RANK is the better choice.

Quick answer

ROW_NUMBER() assigns a sequential number to each row within an ordered window, restarting for every PARTITION BY group. Add a deterministic tie-breaker to ORDER BY, then filter row_number = 1 in an outer query to keep one latest row per group.

FAQs

Does ROW_NUMBER restart for each group?

Yes, when PARTITION BY is present. ROW_NUMBER starts at 1 for each partition. Without PARTITION BY, it numbers the entire result set according to the ORDER BY inside the OVER clause.

Why does ROW_NUMBER give different results for ties?

If the window ORDER BY values tie, the database has no complete ordering between those peer rows. Add a stable, unique tie-breaker such as a primary key when one row must consistently receive row number 1.

Can I use ROW_NUMBER in WHERE?

Not directly in the same query level’s WHERE clause because window functions are evaluated later. Compute ROW_NUMBER in a CTE, subquery, or a dialect feature such as BigQuery QUALIFY, then filter the resulting row number.

What is the difference between ROW_NUMBER and RANK?

ROW_NUMBER gives every row a distinct sequential number. RANK gives equal ORDER BY values the same rank and leaves gaps after ties. DENSE_RANK gives ties the same rank without gaps.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

What does ROW_NUMBER() do?

PostgreSQL defines row_number as the number of the current row within its partition, counting from 1. The same core window-function pattern is supported by SQL Server and BigQuery.

Number each customer’s orders from newest to oldest

SELECT
  customer_id,
  order_id,
  ordered_at,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at DESC, order_id DESC
  ) AS order_number
FROM orders;

How do you return the latest row per group?

Calculate ROW_NUMBER() in a CTE or derived table, then filter after the window function has been evaluated. The secondary order key makes the winner deterministic when two rows share the same timestamp.

Keep one latest order per customer

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_id,
    ordered_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY ordered_at DESC, order_id DESC
    ) AS row_number
  FROM orders
)
SELECT customer_id, order_id, ordered_at
FROM ranked_orders
WHERE row_number = 1;

When should you use ROW_NUMBER instead of RANK?

Use ROW_NUMBER when every row must get a different position, including ties. Use RANK when tied values should share a rank and the next rank should reflect how many rows came before. Use DENSE_RANK when ties share a rank without gaps.

For the larger choice between preserving detail rows with windows and collapsing them with aggregates, read SQL Window Functions vs GROUP BY. BigQuery users can also filter window results directly with

Format multi-line OVER clauses with sqlfmt to make the partition, order, and selected columns easy to audit.

SQL ROW_NUMBER(): How to Number Rows

Quick answer

FAQs

Does ROW_NUMBER restart for each group?

Why does ROW_NUMBER give different results for ties?

Can I use ROW_NUMBER in WHERE?

What is the difference between ROW_NUMBER and RANK?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

What does ROW_NUMBER() do?

How do you return the latest row per group?

Why should ROW_NUMBER() include a tie-breaker?

When should you use ROW_NUMBER instead of RANK?

Quick answer

FAQs

Does ROW_NUMBER restart for each group?

Why does ROW_NUMBER give different results for ties?

Can I use ROW_NUMBER in WHERE?

What is the difference between ROW_NUMBER and RANK?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

What does ROW_NUMBER() do?

How do you return the latest row per group?

Why should ROW_NUMBER() include a tie-breaker?

When should you use ROW_NUMBER instead of RANK?