How can I select multiple columns from a subquery (in SQL Server) that should have one record (select top 1) for each record in the main query?

Question

I know I can select a column from a subquery using this syntax:

SELECT A.SalesOrderID, A.OrderDate,

       (

       SELECT TOP 1 B.Foo

       FROM B

       WHERE A.SalesOrderID = B.SalesOrderID

       ) AS FooFromB

FROM A

WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'

But what is the correct syntax to use multiple columns from a subquery (in my case a select top 1 subquery)? Thank you very much.

Answer 1

If you want to select multiple columns from a subquery:

SELECT

     A.SalesOrderID,

     A.OrderDate,

     SQ.Max_Foo,

     SQ.Max_Foo2

FROM

     A

LEFT OUTER JOIN

     (

     SELECT

          B.SalesOrderID,

          MAX(B.Foo) AS Max_Foo,

          MAX(B.Foo2) AS Max_Foo2

     FROM

          B

     GROUP BY

          B.SalesOrderID

     ) AS SQ ON SQ.SalesOrderID = A.SalesOrderID

If you want to get the values from the row with the highest value for Foo 

(rather than the max of Foo and the max of Foo2 - which is NOT the same thing) then the following query will work better than a subquery:

SELECT

     A.SalesOrderID,

     A.OrderDate,

     B1.Foo,

     B1.Foo2

FROM

     A

LEFT OUTER JOIN B AS B1 ON

     B1.SalesOrderID = A.SalesOrderID

LEFT OUTER JOIN B AS B2 ON

     B2.SalesOrderID = A.SalesOrderID AND

     B2.Foo > B1.Foo

WHERE

     B2.SalesOrderID IS NULL

You're basically saying, give me the row from B where I can't find any other row from B with the same SalesOrderID and a greater Foo.