SQL SERVER: Generate Row Number/Serial Number with each row.

Introduction: In this article i am going to explain how to generate row number or we can say serial number with the result set returned by the query. 

In previous articles i explained How to generate row number/serial number without ordering by any columns and How to use merge in sql to insert, update and delete in single statement and Sql statement equivalent to ternary/conditional operator and Using CTE to get all dates between two specified dates and CASE statement inside in clause (alternative) 

Description: It is very commonly required to generate row number for the result set so that we can loop through the result set or just to show the row number along with each row.

ROW_NUMBER() function is used to generate a serial/row number for a given record set returned by the select query. We have to use ORDER BY clause along with ROW_NUMBER() function to generate row numbers so that the numbers are assigned to the specific order. 

Implementation: Let's understand by an example:

--Create a dummy table 
IF OBJECT_ID('tempdb..#tbEmployee') IS NOT NULL

DROP TABLE #tbEmployee 

CREATE TABLE #tbEmployee

(

EmployeeName VARCHAR(100),

Age INT

)

--Insert some dummy data

INSERT INTO #tbEmployee (EmployeeName,Age)

VALUES

('Neeraj',24),

('Abhay',23),

('Arjun',24),

('Bharat',25),

('Simran',23),

('Kashish',22),

('Rohan',25)

--View table data

SELECT * FROM #tbEmployee

EmployeeName	Age
Neeraj	24
Abhay	23
Arjun	24
Bharat	25
Simran	23
Kashish	22
Rohan	25

--Now let's generate row/serial number along with each row of the result set and order the result by employee name in ascending order.

SELECT ROW_NUMBER() OVER (ORDER BY EmployeeName ASC) AS SrNo,* FROM #tbEmployee

SrNo	EmployeeName	Age
1	Abhay	23
2	Arjun	24
3	Bharat	25
4	Kashish	22
5	Neeraj	24
6	Rohan	25
7	Simran	23

--generate row/serial number along with each row of the result set and order the result by employee name in descending order.

SELECT ROW_NUMBER() OVER (ORDER BY EmployeeName DESC) AS SrNo,* FROM #tbEmployee

SrNo	EmployeeName	Age
1	Simran	23
2	Rohan	25
3	Neeraj	24
4	Kashish	22
5	Bharat	25
6	Arjun	24
7	Abhay	23

--generate row/serial number along with each row of the result set and order the result by age in ascending order.

SELECT ROW_NUMBER() OVER (ORDER BY Age ASC) AS SrNo,* FROM #tbEmployee

SrNo	EmployeeName	Age
1	Kashish	22
2	Abhay	23
3	Simran	23
4	Neeraj	24
5	Arjun	24
6	Bharat	25
7	Rohan	25

--generate row/serial number along with each row of the result set and order the result by age in descending order.

SELECT ROW_NUMBER() OVER (ORDER BY Age DESC) AS SrNo,* FROM #tbEmployee

SrNo	EmployeeName	Age
1	Bharat	25
2	Rohan	25
3	Arjun	24
4	Neeraj	24
5	Abhay	23
6	Simran	23
7	Kashish	22

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post