Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

ROW_NUMBER()

Learn how to use the ROW_NUMBER() Window Function in SQL Server to assign a unique sequential number to each row.

What is ROW_NUMBER()?

ROW_NUMBER() is a SQL Server Window Function that assigns a unique sequential number to each row returned by a query.

The numbering starts from 1 and continues according to the order specified inside the OVER() clause.

It is commonly used for ranking, pagination, duplicate detection and selecting specific rows.

Syntax

ROW_NUMBER()
OVER
(
    ORDER BY column_name
)

The ORDER BY clause determines the order in which the row numbers are assigned.

Basic Example

Suppose we have an Employees table containing employee details.

SELECT
    EmployeeId,
    EmployeeName,
    Salary,

    ROW_NUMBER() OVER
    (
        ORDER BY Salary DESC
    ) AS RowNumber

FROM Employees;

Sample Employee Data

EmployeeId EmployeeName Salary
101 Amit 70000
102 Neha 60000
103 Rahul 80000
104 Priya 65000

Output

Since the query uses ORDER BY Salary DESC, the highest salary receives row number 1.

RowNumber EmployeeId EmployeeName Salary
1 103 Rahul 80000
2 101 Amit 70000
3 104 Priya 65000
4 102 Neha 60000

ROW_NUMBER() with PARTITION BY

PARTITION BY divides the result into groups. ROW_NUMBER() then starts numbering from 1 separately for each group.

SELECT
    EmployeeId,
    EmployeeName,
    Department,
    Salary,

    ROW_NUMBER() OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS RowNumber

FROM Employees;

Practical Example

Suppose employees belong to different departments. We want to find the highest-paid employee in each department.

WITH EmployeeRanking AS
(
    SELECT
        EmployeeId,
        EmployeeName,
        Department,
        Salary,

        ROW_NUMBER() OVER
        (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS RowNumber

    FROM Employees
)

SELECT
    EmployeeId,
    EmployeeName,
    Department,
    Salary

FROM EmployeeRanking

WHERE RowNumber = 1;

This query returns the employee with the highest salary from each department.

Uses of ROW_NUMBER()

  • Assigning sequential numbers to records.
  • Finding the first record from each group.
  • Finding the latest record from each group.
  • Removing duplicate records.
  • Implementing pagination.
  • Finding top records from each category.

Important Points

  • ROW_NUMBER() starts from 1.
  • ROW_NUMBER() requires the OVER() clause.
  • ORDER BY determines the numbering order.
  • PARTITION BY restarts numbering for every group.
  • Each row receives a unique row number within its partition.

Summary

ROW_NUMBER() assigns a unique sequential number to every row according to the specified ordering. When combined with PARTITION BY, the numbering restarts for each group.