SQLServer get top 1 row from subquery

sql, sql-server, sql-server-2008

Solution

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".

Problem

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?

Original source