.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

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
 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend