May 3 2010

SQL: SELECT TOP 1 record with multiple columns using CROSS APPLY

Category: — zvolkov @ 16:50

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!

Tags:

Comments

Comments are closed