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