Outer Apply can be very useful

First, assume we have a query that shows us the value of sales and the number of sales in each state....

Select
    s.State
    Sum(s.Amount) as Amount,
    Count(*) as Number_of_Sales
from 
    Sales s
Group by s.State

Now the boss asks you to include the name of the salesperson with the most sales in each state....

You decide to use a subquery in the select. It looks a bit wonky but it gets the job done:

Select
    s.State,
    Sum(s.Amount) as Amount,
    Count(*) as s.Number_of_Sales,
    BestSalesperson = (Select top(1) Salesperson from Sales where State = s.State group by Salesperson order by Sum(Amount) desc) 
from 
    Sales s
Group by s.State

Great -- now the boss asks you to throw in the amount of sales that the lead salesperson had.

You scratch your head and decide to repeat the same subquery you'd already used....

Select
    s.State,
    Sum(s.Amount) as Amount,
    Count(*) as s.Number_of_Sales,
    BestSalesperson = (Select top(1) Salesperson from Sales where State = s.State group by Salesperson order by Sum(Amount) desc),
    BestSalesperson_Sales = (Select top(1) Sum(Amount) from Sales where State = s.State group by Salesperson order by Sum(Amount) desc) 
from 
    Sales s
Group by s.State

But is there a better way?

Yes there is!

You could've used an OUTER APPLY.... watch

Select
    s.State,
    Sum(s.Amount) as Amount,
    Count(*) as s.Number_of_Sales,
    so.SalesPerson as BestSalesperson,
    so.SalesAmount as BestSalesperson_Sales
from 
    Sales s
outer apply (Select top (1) 
        Salesperson, 
        Sum(Amount) as SalesAmount 
    from 
        Sales 
    where 
        State = s.State 
    Group by 
        SalesPerson 
    order by 
        Sum(Amount) desc) so
Group by State, so.SalesPerson, so.SalesAmount

Outer apply is just the trick.

It lets you create a table that refers to any other table in the query, without having to perform a regular join.

I suppose there are better examples to be created, but I haven't thought of one just yet.

results matching ""

    No results matching ""