ROW_NUMBER in CTE: Why Is It Starting At 12?

• ⚠️ Missing WHERE clauses before ROW_NUMBER() can cause row numbers not to start at 1.
• 📊 Window functions rely strictly on ORDER BY inside the OVER() clause—outer ORDER BY has no effect.
• 💡 Filtering after ROW_NUMBER() assigns numbers before trim, leading to skipped sequences.
• 🔍 Execution plans often defer or reorder steps, impacting window function behavior.
• 🔄 Joins or row duplication can inflate results, leading to higher starting ROW_NUMBER() values.

Why Is ROW_NUMBER Starting at 12 in My CTE?

You're working with SQL. You use a Common Table Expression (CTE) with ROW_NUMBER(). But something is not right. Instead of starting at 1, your row numbers begin at 12. Or worse, 53, 7, 28. This is not what you expect. It messes up your logic for pagination, rankings, or slicing data. If you wonder why ROW_NUMBER() isn’t working as you thought in your CTE, this guide explains the main reasons. It also shows how SQL runs queries. And it shows how to fix these issues.

What Does ROW_NUMBER() Actually Do?

The ROW_NUMBER() function is a window function in SQL. It gives a unique, sequential number to rows in a result set. Its main job is to put rows in a specific order. This is important for ranking, pagination, and checking groups of data.

Here is a basic example:

SELECT 
  employee_id,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
FROM employees;

In this example, ROW_NUMBER() gives numbers like 1, 2, 3, and so on. The order of these numbers depends on hire_date. The ORDER BY clause inside OVER() is needed if you want numbers in a clear order.

If you leave it out, like ROW_NUMBER() OVER (), SQL has no order defined. The rows might get numbers in a random order. This can change each time the query runs.

Anatomy of a Common Table Expression (CTE)

A Common Table Expression (CTE) is a temporary set of results. It has a name and is set up with the WITH clause. It helps make SQL code easier to read. It also lets you keep parts of your logic separate. This is helpful for complicated queries or recursive statements.

CTE Simple Structure:

WITH OrderedEmployees AS (
  SELECT 
    employee_id,
    ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
  FROM employees
)
SELECT * FROM OrderedEmployees
WHERE row_num <= 5;

Here, the CTE OrderedEmployees figures out a row_num based on hire_date. But there's a common problem. ROW_NUMBER() logic inside a CTE may not work as expected when you query it later. This is especially true when other parts of the query filter or reorder data.

CTEs are not saved as actual tables by default. The optimizer might just put their code right into the main query. Or it might change how they run. This is important to know for understanding the ROW_NUMBER() problem.

CTE Ordering Isn’t Always What You Think

Many developers think that sorting inside a CTE stays put. They think it controls the final output. This is wrong. It can cause mistakes in your logic.

CTEs act like regular views. If you don't use an ORDER BY in your final query (after the CTE), the results will not be in any specific order. Even if ROW_NUMBER() uses an ORDER BY inside its OVER() clause, the outer query must handle ordering or filtering to get useful results.

Also, SQL works with sets. Sets do not have an order by nature. So, if you don't force an order where you need it, SQL might change the row sequence each time you run the query.

The Myth of “Natural Order” in SQL Rows

You might think that rows always keep their original order. Maybe by when they were added, or by their ID, or a clustering key. But this is not true. Relying on this "natural order" is risky. It makes query results unclear.

Rows in SQL have no built-in order unless you say so. The database engine arranges data to make things run fast. It might use B-trees, indexes, or hash buckets. These can change as data changes. Steven Feuerstein said:

“Relying on default order from a database is a dangerous practice. ROW_NUMBER will reflect this unpredictability unless explicitly controlled.”
— Feuerstein, 2021

So, even if your data hasn’t changed, a small thing like rebuilding an index or updating statistics can make ROW_NUMBER() give different results.

Why ROW_NUMBER() Might Not Start at 1

Now we look at the main problem: Why does ROW_NUMBER() start higher than 1 in your CTE?

This unexpected result often comes from where things are placed in your code. Or it happens when you misunderstand how SQL runs queries. Here is what usually goes wrong:

1. Filtering After ROW_NUMBER Assignment

Look at this:

WITH RankedCTE AS (
  SELECT 
    employee_id,
    ROW_NUMBER() OVER (ORDER BY hire_date) AS rn
  FROM employees
)
SELECT * FROM RankedCTE
WHERE department_id = 5;

Here, ROW_NUMBER() is given to all employees first. Then, the filtering happens. If the first 11 employees are not in department_id = 5, then the first row you get back will start with the number 12.

2. Logical Mistake With CTE Filters

Applying filters outside the CTE or inside it changes the row numbers. It is best to apply conditions before you assign ROW_NUMBER(). This way, your results will start at 1.

Incorrect:

-- Filter applied after row numbering
WITH CTE AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) AS rn 
  FROM sales
)
SELECT * FROM CTE
WHERE customer_type = 'Retail';

Correct:

WITH CTE AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) AS rn 
  FROM sales
  WHERE customer_type = 'Retail'
)
SELECT * FROM CTE;

The Problem: ORDER BY Placement

Another common reason is that the ORDER BY clause is in the wrong place. Or it is missing entirely from the ROW_NUMBER() window.

Correct way:

ROW_NUMBER() OVER (ORDER BY hire_date)

Incorrect way:

ROW_NUMBER() OVER () -- Order is not set
ORDER BY hire_date  -- This sorts the final list, not the row numbering

The final ORDER BY only changes how the output looks. It does not fix mistakes in how ROW_NUMBER() gives numbers. Itzik Ben-Gan warns:

“The result of ROW_NUMBER depends strictly on the ORDER BY clause supplied in the OVER clause; otherwise, the sequencing is nondeterministic.”
— Ben-Gan, 2019

Always keep in mind: The ORDER BY in the main SELECT just changes how things appear. It does not fix numbering errors.

How Joins and Derived Tables Affect Row Numbers

Your use of joins, especially outer joins, can quietly change ROW_NUMBER() sequences. Look at this:

SELECT 
  e.employee_id,
  d.name,
  ROW_NUMBER() OVER (ORDER BY e.hire_date) AS rn
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

What might go wrong?

• Row Duplication: If one department matches many employees, or the other way around, you might get duplicate rows.
• Too Many Rows: More joins can increase the number of rows before ROW_NUMBER() runs. This leads to higher numbers and gaps you don't expect.

It is a good idea to check your join logic before you use window functions. Check counts. Make sure you know how many rows your joins create. This helps make sure joins do not create too many temporary rows.

Execution Plans: Seeing What SQL Does

SQL optimizers change your query behind the scenes. They reorder steps. They might run parts of the query later. Or they might push filters down to make the query faster. This can happen even if it's not how you thought it would work.

The optimizer might change your well-written code. This is common in big queries with many CTEs, joins, and filters.

Use tools like:

• In SQL Server: Execution Plans in SSMS
• In PostgreSQL: EXPLAIN ANALYZE
• In MySQL: EXPLAIN EXTENDED

These tools help you see how SQL actually runs your logic. Not just how you wrote it. Gorman explains:

“Query execution order—especially in CTEs—affects returned row numbers when using window functions.”
— Gorman, 2020

Use this check to see if your expectations are right. Seeing the real plan helps you fix problems with row numbering and gaps.

Partitioning Can Also Be Confusing

PARTITION BY is another part of ROW_NUMBER(). It can make developers think row numbers start at 1 for the whole data set. But they restart in each group.

Example:

SELECT 
  employee_id,
  department_id,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) as rn
FROM employees;

Here, row numbers restart for each department. If you then filter for just one department, the row numbers might seem to start at 7. But this just means you are seeing rows after a partition. And you are seeing them without a filter.

Make sure any partitioning is followed by filters or GROUP BY clauses that match it. If not, your row numbers might not make sense when taken out of their full context.

How to Debug ROW_NUMBER Issues

You do not need to guess when fixing SQL row numbering problems. Here is a checklist to follow:

1. ✅ Separate the Logic: Take out complex joins and nested subqueries. Test ROW_NUMBER() in a simple query first.
2. 🔍 Filter Before, Not After: Put conditions inside the CTE or subquery that creates the row numbers.
3. 📐 Order Exactly: Always use ORDER BY inside OVER(). Never rely on outside queries to set the order.
4. 🔗 Check Joins: Bad joins can add duplicate rows by mistake.
5. 📊 Use Temp Tables: Save steps in temporary tables. This lets you check row counts and sequences.
6. 🔧 Look at Execution Plans: Confirm that SQL runs your logic in the order you expect.

Good Ways to Use ROW_NUMBER in CTEs

To get ROW_NUMBER() to work right and consistently, you need good habits. Use these ideas to avoid surprises:

• 🧭 Always set a clear ORDER BY clause in every OVER() call.
• 🚫 Never assume a CTE's order. Only an outside ORDER BY will make it so.
• 🔄 Don't filter after ROW_NUMBER() unless you fully understand what will happen.
• 🔍 If you need to filter, do it where ROW_NUMBER() is made.
• 🧵 Use CTEs in layers if your logic gets too complex. Break it down to make it clearer.

Real-World Examples and Fixes

🛑 Problem: Missing ORDER BY

ROW_NUMBER() OVER ()

✅ Fix:

ROW_NUMBER() OVER (ORDER BY created_at)

🛑 Problem: Applying Filters After Numbering

WITH CTE AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) AS rn 
  FROM sales
)
SELECT * FROM CTE
WHERE customer_type = 'Retail';

✅ Fix:

WITH CTE AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) AS rn 
  FROM sales
  WHERE customer_type = 'Retail'
)
SELECT * FROM CTE;

🛑 Problem: Joins Create Too Many Rows

FROM orders o
JOIN customers c ON o.customer_id = c.id

Duplicate rows mean too many results. This makes ROW_NUMBER() unreliable.

✅ Fix:

Use joins carefully with the right keys. Make sure you have one-to-one or many-to-one matches before using window functions.

When To Use Other Ranking Functions

Sometimes, ROW_NUMBER() is not the right tool. Consider these:

Knowing the right window function helps make code better and clearer.

Think Like the Optimizer

SQL is not step-by-step code. It is declarative. You cannot always force a "step-by-step" logic unless the optimizer sees a good reason. To use window functions like ROW_NUMBER() well, you need to know how SQL systems run, reorder, and make your logic faster.

First, remove extra parts. Stack your CTEs correctly. Put the right conditions early. And look at your query plan. Then, you will learn how to guide SQL. You won't struggle with unexpected things.

Citations

Ben-Gan, I. (2019). T-SQL Window Functions. Microsoft Press.
Feuerstein, S. (2021). SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
Gorman, T. (2020). Understanding the Execution Plan in SQL. SQLSaturday.