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.
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.
Read more... Read less...
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'
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
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
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'
select t1.*, sq.* from table1 t1, (select a,b,c from table2 ...) sq where ...