Welcome :Guest

Congratulations!!!

Top 5 Contributors of the Month
Mayuri
Sandeep Singh
dwp19948
stevemarkayden
kumarfurima

 Home >> Articles >> DataBase >> Post New Resource

# ROW_NUMBER()Over(Partition By Column1 Order By Column1))

Posted By:shankey       Posted Date: June 08, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com

User of ROW_NUMBER()Over(Partition By Column1 Order By Column1)) in SQL Server

This article is based on ROW_NUMBER()Over(Partition By Column1 Order By Column1)). To understand the user of this statement let us take an example or the goal we want to achive. I have a list of student name with their roll number. As shown below

Now what is our goal is to have result in which each student name must have the repeatative count appended to their name. As shown below.

Any guy can do this using the cursor and iterate each row along with this maintaining count of each student at each iteration and bla bla bla..

This is the most common answer or solution that any one will suggest. But the easieast and best way to do this is by using ROW_NUMBER()Over(Partition By Column1 Order By Column1)) statement.

Following is the table structure with insert statement for #student temporary table.

CREATE TABLE #student
(
rollnumber INT ,
name VARCHAR(255)
)
INSERT INTO #student
( rollnumber ,
name
)
SELECT  1 ,
'RAKESH'
UNION ALL
SELECT  2 ,
'MAHESH'
UNION ALL
SELECT  3 ,
'RAJESH'
UNION ALL
SELECT  4 ,
'SHANKEY'
UNION ALL
SELECT  5 ,
'RAKESH'
UNION ALL
SELECT  6 ,
'SHANKEY'
UNION ALL
SELECT  7 ,
'RAJKUMAR'
UNION ALL
SELECT  8 ,
'SHANKEY'

Following is the single query which do this with a singleshot.

SELECT rollnumber ,
name + '-'
+ CAST(( ROW_NUMBER() OVER ( PARTITION BY name ORDER BY rollnumber, name ) ) AS VARCHAR(MAX)) ,
ROW_NUMBER() OVER ( PARTITION BY name ORDER BY rollnumber, name ) AS 'Repeatative Count'
FROM  #student
ORDER BY rollnumber

Responses

No response found. Be the first to respond this post

Post Comment