Returning Ranked Results with Microsoft SQL Server 2005

Returning Ranked Results with Microsoft SQL Server 2005

Numbering Rows with ROW_NUMBER

The ROW_NUMBER function assigns an ordinal value with each record returned, with the ordinal values depending on a particular ORDER BY clause used in tandem with the function. The syntax for ROW_NUMBER is: ROW_NUMBER() OVER([partition] order-by-clause)

For example:

Notice that certain customers are on this list multiple times (Nil, Dani, and Jordi). Perhaps rather than seeing all orders, ordered by sales amount, we are interested in seeing the top orders per customer. We can accomplish this with the PARTITION BY clause in the ROW_NUMBER function like so:

SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount,
ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomer
FROM vwTotalAmountBilledPerOrder AS tab
INNER JOIN Orders AS o ON
o.OrderID = tab.OrderID
INNER JOIN Customers AS c ON
c.CustomerID = o.CustomerID

This will return the following results:

Name DateOrdered TotalOrderAmount BestCustomer
Nil 12/1/2005 12649.9900 1
Nil 12/19/2005 265.8500 2
Jordi 12/22/2005 14.9500 1
Jordi 12/18/2005 12.4400 2
Peter 1/2/2006 620.0000 1
Dani 1/5/2006 14.9500 1
Dani 1/4/2006 9.9900 2
MC 1/3/2006 8.5000 1