Click here to Skip to main content
Click here to Skip to main content
Go to top

Row Numbers in SQL Query (Microsoft SQL Server 2000 and 2005)

, 17 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Helps in assigning row numbers in the returned result of an SQL query

Introduction

Sometimes we may need to assign row numbers in the returned result of an SQL query. Since Microsoft SQL server 2000 doesn't support row number function, it has to be achieved through the introduction of IDENTITY column when we use ‘into’ clause in the select statement. The temp table can be populated first. Finally, the temp table can be used to fetch the rows along with the field row number.

Using the Code

The following example demonstrates the method of using temp table to assign row numbers to the result of a select query. Remember to drop the temp table after the select statement; otherwise, it will throw an error indicating the existence of the temp table already when you execute this query next time.

SELECT IDENTITY(int, 1,1) AS RowNumber, EmployeeId INTO #temp _
	FROM EmployeeMaster ORDER BY EmployeeId ASC
SELECT * FROM #Temp ORDER BY RowNumber DROP TABLE #Temp 

The IDENTITY gets three mandatory parameters, namely datatype of the identity column, starting value and the increment. With these, you can customize the new column according to your requirement. For example, an integer starting from 100 and incremented for each row with 2 can be specified as:

IDENTITY(int,100,2)

Obviously, this can be done with Microsoft SQL Server 2005. But the SQL server introduces a new function called row_number(). This function assigns a unique row number starting from 1 to number of records.

The following is the statement which is the SQL Server 2005 equivalent of the above query set:

 SELECT row_number() OVER (ORDER BY EmployeeId) AS RowNumber,  _
	EmployeeID FROM EmployeeMaster 

This statement doesn't use the temp table. So, the fetch will be faster than the previous approach. As in the previous approach, if you want to start the row number from 100, you can use:

row_number() OVER (ORDER BY EmployeeId)+100 as RowNumber

In addition to the row_number() function, SQL Server 2005 also introduces the function rank(). This can be used to rank the records based on another column. An example for the usage of rank function is as follows:

SELECT rank() OVER (ORDER BY JoinDate) AS RowNumber, _
	EmployeeID, JoinDate FROM EmployeeMaster

The above statement gets the rank of each employee based on his/her join date as a new column. If that column is the Key column (such as the EmployeeId), then the row_number() and rank() both produce the same result.

History

  • 17th August, 2009: Initial post 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

P. Ganesh Kumar
Engineer International Flavours & Fragrances India Pvt. Ltd
India India
Ganesh Kumar has done his Bachelor's degree in Computer Science at Bharathiar University, Coimbatore, Master's degree in Computer Applications at IGNOU and M.Tech in Computer Science & Engineering at Dr. MGR University, Chennai, India.
 
He is having hands-on experience in Algorithm implementation, Application design/development for Servers, Desktop and Single Board Computer devices in .Net C#, C, C++.
 
Interested in developing & customizing algorithms.
 
His hobbies include Blogging, Listening to Music and Singing.
 
 Home:        http://www.technicalganesh.com/
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberHerman M. Reyes20-May-11 9:08 
GeneralError in SQL Server 2000 with [modified] PinmemberMassimo Conti14-Oct-10 5:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 17 Aug 2009
Article Copyright 2009 by P. Ganesh Kumar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid