SQL Tips by Namwar Rizvi

August 25, 2007

Return Last n Orders by using APPLY operator

With many other new enhancements, SQL Server 2005
has introduced another very useful operator called
APPLY, which makes life very easy for some complex problems.
APPLY operator works as follows:
1. It applies a table valued function to each row of the table
by using the column values as parameters.
2. Resulting rows are then returned as table
which can be used as a normal table.

Following TSQL script will return Last 3 orders for every
SalesPerson in AdventureWorks database.
Just replace 3 with stored procedure parameter to get
n number of last orders from each sales person.

Use AdventureWorks
Go

–Creating function to
–return top 3 orders

Create Function
dbo.udf_GetLatestOrders(@p_SalesPersonId int)

Returns table
AS
return

Select Top 3 *
from Sales.SalesOrderHeader
Where SalesPersonId=@p_SalesPersonId
Order by ShipDate desc

GO

–Run the query to use above
–created function

Select SP.* from Sales.SalesOrderHeader S
Cross Apply
dbo.udf_GetLatestOrders(S.SalesPersonId) SP

Where
–Join function and Master table
S.SalesPersonId=SP.SalesPersonId

Advertisements

1 Comment »

  1. SELECT DISTINCT SP.* FROM Sales.SalesOrderHeader S
    CROSS APPLY
    dbo.udf_GetLatestOrders(S.SalesPersonID) SP
    WHERE
    –JOIN FUNCTION AND MASTER TABLE
    S.SalesPersonID=SP.SalesPersonID

    Comment by IronMan — July 11, 2008 @ 7:07 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

Create a free website or blog at WordPress.com.

%d bloggers like this: