Inside Microsoft® SQL Server® 2008: T-SQL Querying

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 ...