How to use ROW_NUMBER Function in SQL Server

The numbering starts from 1 for the first row, based on the ordering specified in the ORDER BY clause.

Syntax of the ROW_NUMBER() function:

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

For example:

SELECT ROW_NUMBER() OVER (ORDER BY FirstName) as Order, FirstName, LastName, City
FROM Customers

Result

The ROW_NUMBER() function is useful for pagination, It is a temporary value calculated when the query is run.

Partitioning Data with PARTITION BY

You can also use the PARTITION BY clause to restart the row numbering for each partition of data. This is useful when you want to create separate row numbers for groups of records.

SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    DepartmentID, 
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY LastName ASC) AS RowNum
FROM 
    Employees

In this example:

• The row numbers are assigned for each department (DepartmentID), restarting the numbering for each department.

Filtering Rows Using ROW_NUMBER()

If you want to retrieve specific rows, such as getting only the first few rows for each partition (e.g., top 3 employees in each department), you can use the ROW_NUMBER() function with a WHERE clause.

WITH RankedEmployees AS (
    SELECT 
        EmployeeID, 
        FirstName, 
        LastName, 
        DepartmentID, 
        ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY LastName ASC) AS RowNum
    FROM 
        Employees
)
SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    DepartmentID
FROM 
    RankedEmployees
WHERE 
    RowNum <= 3

In this example:

• We first rank employees by department (PARTITION BY DepartmentID), then filter to get only the top 3 employees (RowNum <= 3) from each department.