143

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397

4 Answers4

155

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
42
SELECT a.salesorderid, a.orderdate, s.orderdate, s.salesorderid
FROM A a
OUTER APPLY (SELECT top(1) *
             FROM B b WHERE a.salesorderid = b.salesorderid) as s
WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'
alex.b
  • 4,547
  • 1
  • 31
  • 52
SANTOSH APPANA
  • 439
  • 4
  • 3
  • 3
    This answer works great when the match from B needs to differ for each A record, which doesn't work with a typical subquery join. – Keith Jul 12 '17 at 20:27
  • 1
    Thank you, outer apply is better than join, because one can reference other parameters as well, which is needed for more complex functions calls etc. – Andreas Reiff Jan 25 '18 at 10:22
  • How to I specify the relationship between the query on A and the apply on B? I would like to select everything from A that is not in B. – Aaron Franke Jun 30 '21 at 15:57
10

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • The first query is clearer. However, if you do a JOIN rather than a LEFT JOIN, if there is no matching record in B, you will lost the record from A. Maybe that's what you want, but it isn't what I'd normally want. Just be aware. – John Deighan May 29 '19 at 17:19
3
select t1.*, sq.*
from table1 t1,
   (select a,b,c from table2 ...) sq
where ...
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
Alexander Lebedev
  • 5,968
  • 1
  • 20
  • 30