SQLServer get top 1 row from subquery

Question

In a huge products query, I'm trying to get the prices for the last buy for every element. In my first approach, I added a sub-query, on the buys table ordering by date descendant and only getting the first row, so I ensure I got the latest row. But it didn't show any values. Now that I see it again, it's logical, as the sub-query still doesn't have a restriction for the product then lists all the buys and gets the latest one, that doesn't have to be for the product currently being processed by the main query. so returns nothing.

That's the very simplified code:

SELECT P.ID, P.Description, P... blah, blah
FROM Products
LEFT JOIN (
    SELECT TOP 1 B.Product,B.Date,B.Price --Can't take out TOP 1 if ORDER BY
    FROM Buys B
    --WHERE... Can't select by P.Product because doesn't exist in that context
    ORDER BY B.Date DESC, B.ID DESC
) BUY ON BUY.Product=P.ID
WHERE (Some product family and kind restrictions, etc, so it processes a big amount of products)

I thought about an embedded query in the main select stmt, but as I need several values it would imply doing a query for each, and that's ugly and bad.

Is there a way to do this and avoid the infamous LOOP? Anyone knows the Good?

Answer 1

You are going down the path of using outer apply, so let's continue:

SELECT P.ID, P.Description, P... blah, blah
FROM Products p OUTER APPLY
     (SELECT TOP 1 B.Product,B.Date,B.Price --Can't take out TOP 1 if ORDER BY
      FROM Buys b
    --WHERE... Can't select by P.Product because doesn't exist in that context
      WHERE b.Product = P.ID
      ORDER BY B.Date DESC, B.ID DESC
     ) buy
WHERE (Some product family and kind restrictions, etc, so it processes a big amount of products)

In this context, you can thing of apply as being a correlated subquery that can return multiple columns. In other databases, this is called a "lateral join".

Answer 2

Seems like a good candidate for OUTER APPLY. You need something along these lines..

SELECT P.ID, P.Description, P... blah, blah
FROM Products P
OUTER APPLY (
    SELECT TOP 1 B.Product,B.Date,B.Price 
    FROM Buys B
    WHERE B.ProductID = P.ID
    ORDER BY B.Date DESC, B.ID DESC
) a