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

User · Answer

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

User · Answer

select t1    sq   from table1 t1      select a b c from table2      sq where

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

[sql] 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?

Examples related to sql