TSQL ROW_NUMBER Function
A great new feature of SQL Server 2005 is the ROW_NUMBER function. In previous versions of SQL to get a numbered row would pretty much require moving the data into a temp table with an identity column, placing an identity column on the original table, or looping through data with a cursor.
SQL Server 2005 makes adding a row number to a query a very simple task with the ROW_NUMBER function. To use this, simply write the query like normal, say Sales Orders by Customer. As one of the columns include the ROW_NUMBER() function with the OVER clause.
SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
ROW_NUMBER() OVER (ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID
The ORDER BY clause sorts the data, in this case by the OrderDate. The results will now return a sequential number based on the OrderDate along with the CustomerID, OrderDate, and AccountNumber.
Another question that might come up is if we wanted to number each row by customer and then start over at the next customer. To do this, the partition attribute must be used as well.
SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
ROW_NUMBER() OVER (PARTITION BY SO.CustomerID ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID
Now the data contains a rownumber (OrderNumber) that resets for each customer.
The results of the two queries are seen below:
SQL Server 2005 makes adding a row number to a query a very simple task with the ROW_NUMBER function. To use this, simply write the query like normal, say Sales Orders by Customer. As one of the columns include the ROW_NUMBER() function with the OVER clause.
SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
ROW_NUMBER() OVER (ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID
The ORDER BY clause sorts the data, in this case by the OrderDate. The results will now return a sequential number based on the OrderDate along with the CustomerID, OrderDate, and AccountNumber.
Another question that might come up is if we wanted to number each row by customer and then start over at the next customer. To do this, the partition attribute must be used as well.
SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
ROW_NUMBER() OVER (PARTITION BY SO.CustomerID ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID
Now the data contains a rownumber (OrderNumber) that resets for each customer.
The results of the two queries are seen below:
If i wanted the row_number() function to start at 15 and not 1, how would i do that?
Reply to this
I like this concept. I visited your blog for the first time and just been your fan. Keep posting as I am gonna come to read it everyday..
Reply to this
row_number() over(order by R.CREATEDATE)+ 14
Reply to this
Admiring the time and effort you put into your blog and detailed information you offer! I didn't know that!
Reply to this
I would like to add your blog to my blogroll please tell me what anchor should I use?
Reply to this
I really like your blog....Its very helpful and keeps me updated...
Thanks for the help...Keep posting more such articles....
4gl tools
Reply to this
Very cool SQL - it's such a rich language - I only know the basics like SELECT UPDATE AND DELETE!
Reply to this
If i wanted the row_number() function to start at 15 and not 1, how would i do that?
Reply to this
My SQL which are too small to be worth posting as downloads.we need to change ROOT_MY SQL_PASSWORD to your root My SQL password, or another user name/password combo with shutdown privileges.we can find out where they are by running which apachectl and which mysqld_safe in the terminal.
http://www.craps-lessons.net
Reply to this
We are a group of volunteers and starting a new initiative in a community. Your blog provided us valuable information to work on. You have done a marvellous job!
Reply to this
Very god. Very helpful
Reply to this
Good article. Looks like my developers are still using SQL Server 2000 coding standards.
Reply to this
i want to develop an web service using C#.i am going to fetch large amount of data from server which is in some other country.is there any way to compress the data? i am using SQL 2000.
Reply to this
yes there are some latest version of SQL.SERVER..... i think it dosen't need any compress.....
Reply to this
your article is so informative and interesting. nice shared.
Reply to this
It's nice to see a detailed explanation about the Analytical Functions by considering our old "EMP" database as the source.
Reply to this
My SQL which are too small to be worth posting as downloads.we need to change ROOT_MY SQL_PASSWORD to your root My SQL password, or another user name/password combo with shutdown privileges.we can find out where they are by running which apachectl and which mysqld_safe in the terminal.
Reply to this
This is my first time i have visited here. I found many interesting stuffs
Reply to this
Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post.
Reply to this
I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
Reply to this
I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.
Reply to this
Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts.Any way Ill be subscribing to your feed and I hope you post again soon
Reply to this
I found this informative and interesting blog i think its very useful and knowledge able.I would like to thank you for the efforts you have made in writing this article.
Reply to this
I will recommend my friends to read this.I will bookmark your blog and have my children check up here often.I am quite sure they will learn lots of new stuff here than anybody else!....
http://www.tutorialcasino.net
Reply to this
your article is so informative and interesting. nice shared.
Reply to this
i wish i could help you but i am not much into sql server 2008, i basically work on sql server 2000 and sql server 2005,i have just started learning the concepts of sql server 2008
Reply to this
I want to develop an web service using C#.i am going to fetch large amount of data from server which is in some other country.is there any way to compress the data? i am using SQL 2000.
Reply to this
If i wanted the row_number() function to start at 15 and not 1, how would i do that?
Reply to this
Instead of SQL 2008,2005 is more better and good but even though i started to learn 2008...i think this site would help me a lot,i will refer this site..thank you....
Reply to this
thanks for your post i was looking for something like this. Ill be subscribing to your RSS feed.
Reply to this
SQL totally a different language,but it plays a vital role for maintain a database records,as per me this iste really helps me a lot.thank you for sharing
Reply to this
There is a new feature in SQL Server 2005 that is akin to the ROWNUM function in Oracle which I am going to blog about here. I'm sure alot of people reading will already know about this function and its syntax/behaviour but this post may still serve as a useful reference. I'm mainly writing this for my colleagues at Conchango that won't have come across it yet.
Reply to this
the casino floor, Fox 29's Steve Keeley reported from outside the Pennsylvania Convention Center. And earlier in the morning potential vendors are
Reply to this
The input value and format pattern string must both be present or an error will be thrown. If the input value cannot be coerced to a number by using the rules in the number() function, the format-number function returns NaN. If the format pattern string is not a valid pattern string, the function returns the input value, unchanged.
Reply to this