Using CROSS APPLY (t-sql)

Using CROSS APPLY (t-sql)
Given:

CREATE FUNCTION dbo.fn_GetTopOrders(@custid AS int, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) *
FROM Sales.SalesOrderHeader
WHERE CustomerID = @custid
ORDER BY TotalDue DESC
GO

which returns the top n orders per customerID



SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
CROSS APPLY
AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY
CustomerID ASC, TotalDue DESC


results in this...



CustomerID SalesOrderID TotalDue
----------- ------------ ---------------------
1 45283 37643.1378
1 46042 34722.9906
1 44501 26128.8674
2 46976 10184.0774
2 47997 5469.5941
2 57044 4537.8484
3 53616 92196.9738
3 47439 78578.9054
3 48378 56574.3871
4 47658 132199.8023
. . .




The APPLY clause acts like a JOIN without the ON clause comes in two flavors: CROSS and OUTER.
The OUTER APPLY clause returns all the rows on the left side (Customers)
whether they return any rows in the table-valued-function or not (the "LEFT JOIN" table).

The columns that the table-valued-function returns are null if no rows are returned.

The CROSS APPLY only returns rows from the left side (Customers)
if the table-valued-function returns rows.

Notice that I'm just passing in the CustomerID to the function.
It returns the TOP 3 rows based on the amount of the order.

Since I'm using CROSS APPLY a customer without orders won't appear in the list.
I can also pass in a number other than 3 to easily return a different number of orders per customer.

Even better I can pass in a different number of orders for each customer.
So I could list the top 5 orders for one type of customer but the top 10 for another type of customer.

How cool is that?!?

And it gets even better.
Remember the function has an ORDER BY in it.
It will always return the top orders based on the amount.
However you can change the ORDER BY clause in the query that calls the function to display those rows in whatever order you want.
You could easily display the top 3 orders in ascending order instead.