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
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
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
Very god. Very helpful
Reply to this
Good article. Looks like my developers are still using SQL Server 2000 coding standards.
Reply to this