SQL Tips by Namwar Rizvi

May 29, 2007

Finding N most recent orders of each customer – Wonders of Rom_Number()

Filed under: New Features,Row_Number,SQL Server 2005,tips,TSQL — namwar @ 8:30 PM

Retrieving n most recent orders from each customer is a quite frequent requirement from management reports. Before SQL Server 2005, there were tricky and sometimes unmanageable TSQL solutions for this problem. In SQL Server 2005, you can use Row_Number() to get the n most recent orders. Following query uses AdventureWorks database to demonstrate the solution.

Use AdventureWorks
Go

Select CustomerID,OrderDate,SalesOrderNumber
From
(
Select Row_Number()
OVER (Partition by CustomerId Order by OrderDate Desc ) as RowNo,
CustomerID,OrderDate,SalesOrderNumber
from Sales.SalesOrderHeader
) A

Where A.RowNo < 3

How it works:
1. Firstly, the inner query sorts the order of each customer in descending order of OrderDate
2. It assigns a RowNumber to each order from latest to oldest for each customer.
3. RowNumber will become reset for each new customer.
4. After above steps, result will be returned to Outer query.
5. Outer Query just selects only those rows which have RowNumber less than n(here n is 3)

Possible Enhancements:

1. You can change hard coded 3 to a stored procedure parameter and can convert this query to stored procedure for any number of recent orders for each customers.
2. By changing the OVER clause with different combinations of sorting orders and columns, you can get n latest due orders etc. Possibilities are unlimited.

Advertisements

1 Comment »

  1. This code saved me tons of time. Super. Thank you. 🙂

    Comment by Suneetha — December 18, 2009 @ 4:53 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: