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