View Categories

Explain how ROW_NUMBER() works in SQL.

Parthiban Kannan
Updated on December 2, 2025

1 min read

ROW_NUMBER() is a window function in SQL that assigns a unique sequential number to each row within a result set, based on a specified order. It’s especially useful when you need to rank, filter, or identify specific rows — like finding the top N records in each group or removing duplicates.

For example, if I want to assign a row number to employees based on their salary in descending order, I can write:

🗃️
filename.sql 
SELECT 
    EmployeeName,
    DepartmentID,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum
FROM Employees;

Here’s what happens:

  • The PARTITION BY divides the data into groups — in this case, each department.
  • The ORDER BY inside the OVER() clause determines the order of numbering within each group.
  • The ROW_NUMBER() then assigns sequential numbers (1, 2, 3, …) to each row in that partition.

So, if Department A has five employees, they’ll get row numbers 1 through 5 based on their salary order, and the numbering restarts for Department B.

I’ve used this function often for de-duplication tasks. For instance, when a table has duplicate customer records, I can use ROW_NUMBER() to identify and delete duplicates while keeping the latest record:

🗃️
filename.sql 
WITH DuplicateCTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CreatedDate DESC) AS rn
    FROM Customers
)
DELETE FROM DuplicateCTE WHERE rn > 1;

This ensures only one record per customer (the most recent one) remains.

One challenge I’ve faced is that ROW_NUMBER() doesn’t handle ties automatically — it always gives unique numbers even if two rows have the same value. If I want to handle ties (like giving the same rank for equal salaries), I’d use RANK() or DENSE_RANK() instead.

A limitation is that since it’s a window function, it’s available only in SQL databases that support analytic functions (like SQL Server, PostgreSQL, Oracle, etc.).

But overall, ROW_NUMBER() is one of the most powerful tools for ordering, ranking, and data cleanup — and I’ve applied it frequently in data migration, audit reporting, and performance analytics queries.

Scroll to Top