How to Use ROW_NUMBER() in SQL Server

Galaxy Glossary

How do I generate sequential row numbers in SQL Server queries?

ROW_NUMBER() adds a sequential integer to rows in the result set based on the ORDER BY clause within an OVER() window.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why use ROW_NUMBER() instead of IDENTITY?

ROW_NUMBER() creates a temporary, query-level sequence without altering tables. Use it for ranking, pagination, and deduplication when you cannot—or should not—add a physical column.

What is the basic syntax?

ROW_NUMBER() operates as a window function: ROW_NUMBER() OVER (ORDER BY column_list). The ORDER BY inside OVER() determines row numbering, independent of the query’s outer ORDER BY.

How do I paginate customer orders?

Assign numbers, then filter: WITH ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY order_date DESC) AS rn, * FROM Orders) SELECT * FROM ordered WHERE rn BETWEEN 11 AND 20; This returns page 2 (rows 11-20) of recent orders.

How can I remove duplicate customer emails?

Rank duplicates, keep the first: WITH dup AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn FROM Customers) DELETE FROM dup WHERE rn > 1; Only the earliest sign-up per email remains.

Can I mix PARTITION BY and ORDER BY?

Yes. PARTITION BY resets numbering per group while ORDER BY defines order inside each group. Example: number orders per customer.

Example

SELECT customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq FROM Orders;

Best practices for performance?

Create indexes that support the ORDER BY columns. Avoid functions or expressions that disable index usage. Keep the window frame simple.

What common mistakes should I avoid?

1. Omitting ORDER BY inside OVER(). Without it, numbering order is undefined. 2. Filtering on the same rowset before numbering. Always generate ROW_NUMBER() first, then filter by the generated alias in an outer query or CTE.

Quick reference recap

Use ROW_NUMBER() for ranking, pagination, and deduplication. Combine with PARTITION BY for per-group sequences. Always specify ORDER BY, and filter in an outer query for predictable results.

Why How to Use ROW_NUMBER() in SQL Server is important

How to Use ROW_NUMBER() in SQL Server Example Usage


-- Show a running number per customer’s orders
SELECT 
    o.customer_id,
    c.name AS customer_name,
    o.order_date,
    o.total_amount,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS order_seq
FROM   Orders o
JOIN   Customers c ON c.id = o.customer_id
ORDER  BY o.customer_id, order_seq;

How to Use ROW_NUMBER() in SQL Server Syntax


ROW_NUMBER() OVER ( [PARTITION BY partition_col1, partition_col2, ...] ORDER BY sort_col1 [ASC|DESC], sort_col2, ...) 

-- Pagination example
WITH paged_orders AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY order_date DESC) AS rn,
        id, customer_id, order_date, total_amount
    FROM Orders
)
SELECT *
FROM   paged_orders
WHERE  rn BETWEEN @start_row AND @end_row;

-- Deduplication example
WITH cte AS (
    SELECT 
        id, name, email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM Customers
)
DELETE FROM cte WHERE rn > 1;

Common Mistakes

Leaving out ORDER BY inside OVER(). Without it, SQL Server assigns an arbitrary sequence, causing inconsistent results. Always include an explicit ORDER BY.
Filtering the CTE or subquery before numbering. This skips rows and breaks pagination. Generate ROW_NUMBER() first, then filter on the rn alias in an outer query.

Frequently Asked Questions (FAQs)

Does ROW_NUMBER() require a unique ORDER BY column?

No, but duplicate sort values lead to non-deterministic numbering. Add tiebreaker columns for consistent results.

Can I reference rn in the same SELECT where it’s created?

No. Use a subquery or CTE because window function aliases are computed after the SELECT list.

Is ROW_NUMBER() slower than IDENTITY?

For read-only queries, ROW_NUMBER() is efficient. IDENTITY is faster for permanent sequencing but requires schema changes.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie