Solutions to Common Problems Using TOP and APPLY

Now that I’ve covered the fundamentals of TOP and APPLY, I’ll present common problems and solutions that use TOP and APPLY.

TOP n for Each Group

In Chapter 6, and Chapter 8, I discussed a problem involving tiebreakers in which you were asked to return the most recent order for each employee. This problem is actually a special case of a more generic problem in which you are after the top n rows for each group—for example, returning the three most recent orders for each employee. Again, orders with higher orderdate values have precedence, but you need to introduce a tiebreaker to determine precedence in case of ties. Here I’ll use the maximum orderid as the tiebreaker. I’ll present solutions to this ...

Get Inside Microsoft® SQL Server® 2008: T-SQL Querying now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.