Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to Generate Row Numbers using SQL 2005

Author: DevASP

In this article I will discuss the analytical function ROW_NUMBER () that is introduced in SQL Server 2005. This function returns the sequential number.

T-SQL is very strong and efficient language for accessing the data from database. SQL Server 2005 has make the T-SQL more stronger by adding new functions and features that helps in making task more easier while creating the store procedure, triggers or user defined functions (UDF). .Net Framework provides stronger support in creating algorithms, logical functions etc. using either C# or VB.Net but we cannot always relay on .Net Framework or vise versa means SQL Server 2005.

 

In T-SQL 2005 we find new analytical function ROW_NUMBER () which generates the sequential number in order which we define in order by clause. The syntax of ROW_NUMBER () function is:

 

ROW_NUMBER () OVER (ORDER BY <order by column name>)

 

or

 

ROW_NUMBER () OVER (PARTITION BY <partition by column name>)

 

In row number you can use both i.e., PARTITION BY and ORDER BY combine but ORDER BY clause is compulsory. SO your final syntax will be like as:

 

ROW_NUMBER () OVER ([partition by clause] <order by clause<)

ORDER BY clause is compulsory because this clause determines the sequence in which the row number is assigned to selected row set.

Consider the following example which selects the employee id and show the row number:

 

EXAMPLE:

 

USE TESTDB

 

GO

 

SELECT ROW_NUMBER() OVER (ORDER BY empfirstname) AS ROW, EmpID FROM employees

Article Comments
Thanks ...really helpful for me ...

Posted on 3/26/2008 1:31:51 AM by Anish R

thanks this helped me
this can be even achieved by
rank
but put all key feild in order by

Posted on 5/23/2009 5:12:49 AM by Devang

Thank you your information

Posted on 12/11/2009 3:04:48 AM by TA

really nice information.

Thanks a lot..

Q. can be use this function without column name..

Posted on 12/21/2009 1:51:28 AM by Rajeev

HI Rajeev,

You can use it like this also

SELECT ROW_NUMBER() OVER (ORDER BY empfirstname) AS ROW FROM employees

Thnaks.

Posted on 12/21/2009 4:35:53 AM by zunnair

Send me an email about this article when other users post a Comment

Posted on 2/3/2010 4:16:29 AM by classifieds

Hey .. this i very simple .. and really helped me .. thanks a lot ... for this free post
Nirmal

Posted on 3/31/2010 5:15:24 AM by Nirmal

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to Write Store Procedure (SP) in SQL Server

Disclaimer - Privacy
© 2002-2017 DevASP.net