SQLServer get top 1 row from subquery
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".
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