ROW_Number () in SQL

At its core, ROW_NUMBER() is a window function that assigns a unique, sequential integer to each row within a specific partition (group) of a result set.

The logic of its usage almost always revolves around isolating specific rows based on their order within a group.

Here is a breakdown of the logic behind its most common use cases, along with how it works.

The Fundamental Logic (Syntax Breakdown)

The logic of ROW_NUMBER() is dictated by its two main clauses: PARTITION BY and ORDER BY.

ROW_NUMBER() OVER (
    PARTITION BY column_A   -- 1. How do we group the data?
    ORDER BY column_B DESC  -- 2. How do we order the rows within that group?
)

• PARTITION BY (The Grouping Logic): This tells SQL when to reset the counter back to 1. If you partition by customer_id, the row number starts at 1 for the first customer, counts up, and resets to 1 as soon as it hits the next customer. (This is optional; if omitted, it treats the whole table as one partition).
• ORDER BY (The Sorting Logic): This tells SQL which row gets number 1, which gets 2, etc. This is mandatory, because a row number without a defined order is meaningless.

1. The “Deduplication” Logic (Finding the Latest/First Record)

This is arguably the most common use case. When you have multiple records for the same entity (e.g., a customer has multiple addresses or status updates) and you only want the most recent one.

The Logic: Group the data by the unique identifier, sort it by date (newest first), and then filter for the rows that were assigned the number 1.

Example: Find the most recent purchase for each customer.

WITH RankedPurchases AS (
    SELECT 
        customer_id,
        purchase_date,
        purchase_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) as rn
    FROM purchases
)
SELECT customer_id, purchase_date, purchase_amount
FROM RankedPurchases
WHERE rn = 1;

2. The “Top-N per Category” Logic

If you need to find the top performers within specific sub-categories (e.g., the top 3 highest-paid employees in each department).

The Logic: Group by the category (department), order by the metric descending (salary), and filter where the row number is less than or equal to N (3).

Get Chittamuri Santosh’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Example: Find the top 3 highest-earning employees per department.

WITH RankedEmployees AS (
    SELECT 
        department_name,
        employee_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_name ORDER BY salary DESC) as rn
    FROM employees
)
SELECT department_name, employee_name, salary
FROM RankedEmployees
WHERE rn <= 3;

3. The “Pagination” Logic

When building applications, you rarely want to load 10,000 records at once. You want to load them in “pages” (e.g., rows 1–50, then rows 51–100).

The Logic: Assign a sequential number to the entire dataset (no PARTITION BY needed, just an ORDER BY), and then filter for a specific range of row numbers.

Example: Get page 2 of a product list (assuming 10 products per page).

WITH NumberedProducts AS (
    SELECT 
        product_id,
        product_name,
        price,
        ROW_NUMBER() OVER (ORDER BY product_name ASC) as rn
    FROM products
)
SELECT product_id, product_name, price
FROM NumberedProducts
WHERE rn BETWEEN 11 AND 20;

(Note: Modern SQL dialects often use OFFSET and FETCH for pagination, but ROW_NUMBER() is the traditional, universally supported method).

4. The “Generating Unique IDs” Logic

Sometimes you receive flat data that lacks a primary key, and you need to assign a unique identifier to every single row for processing purposes.

The Logic: Simply order by a column (or even a generic constant if you just need arbitrary numbering) and let ROW_NUMBER() generate a sequence from 1 to n.

SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as surrogate_key,
    *
FROM raw_staging_table;

Logic Check: ROW_NUMBER() vs. RANK()

To truly understand the logic of ROW_NUMBER(), it helps to see how it differs from its sibling, RANK().

Imagine two employees in the same department have the exact same salary:

• ROW_NUMBER() doesn't care about ties. It will arbitrarily give one employee 1 and the other 2. The numbers are always strictly sequential.
• RANK() respects ties. It will give both employees 1, and the next employee in line will get 3.

Therefore, you use ROW_NUMBER() when you strictly need one row per designated position, regardless of ties.