How often in SQL do you have to get the most recent child record of a given master record? Pretty damn often. The simplest solution is usually to use a correlated subquery (basically, a subselect inside the column list of the SELECT clause) with a TOP 1 / ORDER BY. However, this won’t work if you need multiple columns from the child table. What to do? Resort to joins and group-by’s, or perhaps, the mighty ROW_NUMBER? Not so fast. There’s a neat intermediate solution, using CROSS APPLY.
The idea is to use obscured and badly documented ability of CROSS APPLY to take a correlated subquery as its right argument. Yes, that’s right, CROSS APPLY is not limited to table-value functions. Here’s how.
Let’s say you have two tables: Products and ProductPrices. Your task is for each Product to retrieve the most recent ProductPrice. Your query would look like the following:
SELECT
p.ProductID,
p.ProductName,
pp.PriceDate,
pp.Price
FROM Products p
CROSS APPLY (SELECT TOP 1
PriceDate,
Price
FROM ProductPricess pp
WHERE pp.ProductID = p.ProductID
ORDER BY PriceDate DESC) pp
Cool? Yeah, I thought so too!
