ROW_NUMBER

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Number rows within each city using a stable tie-breaker.

Run the example

SELECT TOP (8) CustomerID, City,
       ROW_NUMBER() OVER (PARTITION BY City ORDER BY CustomerID) AS CityRow
FROM dbo.Customers
ORDER BY City, CustomerID;

Read it step by step

1. PARTITION BY City restarts numbering for each city.
2. ORDER BY CustomerID inside OVER determines the row numbers with a unique key.
3. The final ORDER BY arranges the visible result; TOP (8) applies after numbering.

Check the result

CityRow restarts at 1 for each city.

Practice

Number products within each CategoryID by ProductID and show Products 1–5.

SELECT TOP (5) ProductID, CategoryID,
       ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ProductID) AS CategoryRow
FROM dbo.Products
ORDER BY ProductID;