SQL Server 2005 ROW_NUMBER() function

Here's an example of how to use the row_number() function which became available in SQL Server 2005.

Click here for a detailed description of the syntax.
Click here for a Google search on this topic.

Run this script against the AdventureWorks sample database in SQL Server 2005. See how the row_number() function numbers the line items per sales order:

select
SalesOrderNumber,
LineItemNo = row_number() over(partition by SalesOrderNumber order by SalesOrderDetailID),
Product = p.Name,
OrderQty,
UnitPrice,
UnitPriceDiscount,
LineTotal
from
Sales.SalesOrderHeader h
inner join
Sales.SalesOrderDetail d
on
d.SalesOrderID = h.SalesOrderID
inner join
Production.Product p
on
p.ProductID = d.ProductID