SQLServer gets first row from subquery
In a huge product query, I'm trying to get the last purchase prices for each item. In my first approach, I added a subquery to buy the descendant date ordering table and only get the first row, so I guarantee that I got the last row. But it didn't show any values. Now that I see it again, it makes sense, since the sub-request still has no product restrictions and then it lists all purchases and gets the last one that shouldn't be for the product the main request is currently processing. so it returns nothing.
This is a 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 was thinking of an inline query in the main select stmt, but since I need multiple values, that would mean executing the query for each, and that's ugly and bad.
Is there a way to do this and avoid the infamous LOOP? Does anyone know good?
source to share
You are going down the path of use outer apply
, so 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
ORDER BY B.Date DESC, B.ID DESC
WHERE b.Product = P.ID
) buy
WHERE (Some product family and kind restrictions, etc, so it processes a big amount of products)
In this context, you can use it apply
as a correlated subquery that can return multiple columns. In other databases, this is called a "side join".
source to share