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?

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.

From stackoverflow

• select t1.*, sq.*
  from table1 t1,
     (select a,b,c from table2 ...) sq
  where ...
  

• You'll have to make a join:

  SELECT A.SalesOrderID, B.Foo
  FROM A
  JOIN B bo ON bo.id = (
       SELECT TOP 1 id
       FROM B bi
       WHERE bi.SalesOrderID = a.SalesOrderID
       ORDER BY bi.whatever
       )
  WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'
  

  , assuming that b.id is a PRIMARY KEY on B

  In MS SQL 2005 and higher you may use this syntax:

  SELECT SalesOrderID, Foo
  FROM (
    SELECT A.SalesOrderId, B.Foo,
           ROW_NUMBER() OVER (PARTITION BY B.SalesOrderId ORDER BY B.whatever) AS rn
    FROM A
    JOIN B ON B.SalesOrderID = A.SalesOrderID
    WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'
  ) i
  WHERE rn
  

  This will select exactly one record from B for each SalesOrderId.

• Here's generally how 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 what you're ultimately trying to do is 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 will usually 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.

• Very nice post. I like the last one.