SQL Server ROW_NUMBER(): Practical Examples

ROW_NUMBER() is a SQL Server window function that assigns a sequential number to rows according to an ordering rule.

🔢 Basic Example

SELECT
    EmployeeId,
    EmployeeName,
    ROW_NUMBER() OVER (ORDER BY EmployeeName) AS RowNum
FROM Employees;

The numbering starts at 1 and follows the specified ordering.

📂 Partition Rows into Groups

SELECT
    DepartmentId,
    EmployeeName,
    ROW_NUMBER() OVER (
        PARTITION BY DepartmentId
        ORDER BY EmployeeName
    ) AS RowNum
FROM Employees;

With PARTITION BY, numbering restarts for each department.

⭐ Get the Latest Row per Group

WITH Ranked AS
(
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerId
               ORDER BY CreatedDate DESC
           ) AS rn
    FROM Orders
)
SELECT *
FROM Ranked
WHERE rn = 1;

This is one of the most useful real-world patterns: rank rows within each group and keep the first row.

🧹 Find Duplicates

You can also partition by the columns that define a duplicate and inspect rows where the generated number is greater than one.

⚠️ ROW_NUMBER vs RANK

ROW_NUMBER() always produces unique sequential numbers. RANK() gives tied rows the same rank and leaves gaps after ties.

🧠 Key Takeaway

ROW_NUMBER is especially useful when you need deterministic ordering inside groups and want to select, filter, or inspect a specific row from each group.

Continue with 🛢️ SQL Server Tutorials.

🤖 AlgoLassi Assistant Have a question about this tutorial?

Ask AlgoLassi and get an answer plus the tutorials worth studying next.

Ask a question

💬 Comments

Comments will appear here when available.