Advertisement
Advertisement


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.

2009/07/17
1
111
7/17/2009 11:37:34 AM


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'
2016/05/06

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.

2009/02/25

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'
2015/07/08

select t1.*, sq.*
from table1 t1,
   (select a,b,c from table2 ...) sq
where ...
2018/12/06

Source: https://stackoverflow.com/questions/583954
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]