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.
This question is related to
sql
select t1.*, sq.*
from table1 t1,
(select a,b,c from table2 ...) sq
where ...
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'
i think that is what you want.
SELECT
A.SalesOrderID,
A.OrderDate,
FooFromB.*
FROM A,
(SELECT TOP 1 B.Foo
FROM B
WHERE A.SalesOrderID = B.SalesOrderID
) AS FooFromB
WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'
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.
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
.
Source: Stackoverflow.com