Itzik is my mentor. I can't get enough of his articles.
Thanks Itzik and God bless you!

RON9/7/2007 2:24:40 PM

> "Try to solve the problem by using a set-based solution in SQL Server 2000, and you'll find it extremely hard to find a solution."

it's hard only if the table "sales" has a composite key. Adding column ID INT IDENTITY makes it easy:

select stores.stor_id,sales.*
from stores inner join sales on stores.stor_id=sales.stor_id
and sales.id in (select top 2 id from sales where qty >= 10 and stor_id=stores.stor_id
ORDER BY ord_date DESC, ord_num DESC, title_id DESC)


I think the main advantage of APPLY is that it allows participant of a join to be dynamic, i.e. different for every other row of other tables in the join: in this example, previously only subselect in WHERE could refer to the store_id; with APPLY, this subselect becomes an equal participant of the join; so each row of the left table "stores" is joined with another table on the right.

Vadim10/29/2005 8:30:11 AM

Good stuff.

Anonymous User 8/25/2005 1:58:34 PM