Click here to Skip to main content
15,867,750 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
4.82/5 (11 votes)
17 Aug 2009CPOL2 min read 155K   20   3
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.

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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)


Written By
Software Developer (Senior) Infence Technologies
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.infence.com/

Comments and Discussions

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

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

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