How To Use ROW_NUMBER Function in SQL
ROW_NUMBER() function is used to assign row number in sequence from 1 and increment it by 1 for every new record in SELECT statement in SQL.
ORDER BY clause is mandatory along with the OVER clause.
If we omit the OVER clause then we get an error The function ‘ROW_NUMBER’ must have an OVER clause.
If we omit the ORDER BY clause inside the OVER clause then we get the error The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.
PARTITION BY clause is optional. We use partition by clause inside the OVER clause to partition the data. When we partition the data the ROW_NUMBER() function reassigns the sequential number from 1 for every new partition.
Let’s create a table tblEmployee and see how ROW_NUMBER() function works.
USE HRDB GO CREATE TABLE tblEmployee ( EmpID INT PRIMARY KEY IDENTITY(1,1) NOT NULL, EmpFirstName VARCHAR(20) NOT NULL, EmpLastName VARCHAR(20) NULL, EmpUserName VARCHAR(20) UNIQUE NOT NULL, EmpGenderID INT FOREIGN KEY REFERENCES tblGender(GenderID), EmpDeptID INT FOREIGN KEY REFERENCES tblDepartment(DeptID), EmpAge INT CONSTRAINT CHK_tblEmployee_EmpAge CHECK(EmpAge>=18), EmpSalary INT NULL, EmpJoiningDate DATETIME CONSTRAINT DF_tblEmployee_EmpJoiningDate DEFAULT GETDATE() ) GO
USE HRDB GO INSERT INTO tblEmployee (EmpFirstName, EmpLastName, EmpUserName, EmpGenderID, EmpDeptID, EmpAge, EmpSalary, EmpJoiningDate) VALUES ('Davio','Nancy','Davio Nancy',1,1,25,50000,'2010-01-01'), ('Fuller','Andrew','Fuller Andrew',1,1,20,40000,'2010-06-01'), ('Leverling','Janet','Leverling Janet',1,2,35,50000,'2010-07-01'), ('King','Robert','King Robert',1,3,22,25000,'2010-01-01'), ('Ali','Hassan','Ali Hassan',1,1,30,75000,'2010-08-01'), ('Zahid','Ali','Zahid Ali',1,2,25,50000,'2011-01-01'), ('Sara','Ali','Sara Ali',2,3,25,50000,'2011-09-01'), ('Suman','Shah','Suman Shah',2,1,20,30000,'2011-12-01'), ('Anne','Andrew','Anne Andrew',2,3,33,35000,'2012-01-01'), ('Ammara','Khan','Ammara Khan',2,2,25,50000,'2013-01-01') GO
ROW_NUMBER() function without PARTITION BY clause with all columns
USE HRDB GO SELECT *, ROW_NUMBER() OVER(ORDER BY EmpID ASC) AS RowNumber FROM tblEmployee GO

ROW_NUMBER() function without PARTITION BY clause with selected columns
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpSalary, EmpJoiningDate, ROW_NUMBER() OVER(ORDER BY EmpID ASC) AS RowNumber FROM tblEmployee GO

ROW_NUMBER() function with PARTITION BY clause
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpSalary, EmpJoiningDate, ROW_NUMBER() OVER(PARTITION BY EmpGenderID ORDER BY EmpID ASC) AS RowNumber FROM tblEmployee GO

ROW_NUMBER() function with PARTITION BY and WHERE clause
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpSalary, EmpJoiningDate, ROW_NUMBER() OVER(PARTITION BY EmpGenderID ORDER BY EmpID ASC) AS RowNumber FROM tblEmployee WHERE EmpID%2=0 GO

Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL