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