“The APPLY operator is one of the most powerful tools I know of in T-SQL, and yet it seems to be unnoticed by many … it can be used in very creative ways to solve all kinds of querying tasks” said Ikzik Ben-Gan in his book T-SQL Querying by Microsoft Press on page 218.

Top N per group – Two Tables

This post demonstrates a query that returns the TOP N T2.Column for each T1.Unqid. T1 is table one that is the “one” side of a one-to-many relationship with T2, table two. This is the TOP N per group task. The OUTER APPLY is briefly discussed below in this post.

Top N per group – One Table

What if you are not using a one-to-many relationship? Suppose you only have one table where you have a list of people and quantities where the people repeat (duplicates) many times. You need a query showing the top 2 quantities for each person. You can use a window function row_number as demonstrated in the post SQL Server Top N per group Single Table.

One-to-many Relationship

For example, T1 could be a Customers table and T2 could be an Orders table. Customers have many orders. You might need a query that gives you the top 2 most recent orders for each customer.

Using the general form mentioned above, here below are the data in the two SQL tables: T1 and T2.

We will use aliases in this query. Thinking of our customers and orders example above, we will alias T1 with C and T2 with O.

select C.t1Unqid, A.t2Unqid, A.t2date
from T1 as C
cross apply (
   select top(1) t2Unqid, t2date
   from T2 as O
   where O.t1id = C.t1Unqid
   order by t2date DESC, t2Unqid DESC) as A

Our query works. Below are the results in a screen shot of SSMS.

The above query just show the most recent order for each customer, which happened to be on the same date. Now we will show the top 2 by changing the 1 to a 2, and then to a 3. If we change it to show the top 4, the results will be the same as the top 3 because no customer has ordered that many times. Also notice that customer 103 is nowhere in the results because that customer placed no orders. Below are the results of the two queries (top 2 and top 3).

OUTER APPLY

If we change our query above to an outer query, we will see all of the customers who didn’t place orders along with the other results.

select C.t1Unqid, A.t2Unqid, A.t2date
from T1 as C
outer apply (
   select top(2) t2Unqid, t2date
   from T2 as O
   where O.t1id = C.t1Unqid
   order by t2date DESC, t2Unqid DESC) as A

Below are the results in SSMS.