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. 


DescriptionIt 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. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon