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

by

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.

More Related Contents:

  1. query sql between two tables [duplicate]
  2. How does database indexing work? [closed]
  3. How to update two tables in one statement in SQL Server 2005?
  4. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  5. Concatenate columns in Apache Spark DataFrame
  6. Passing multiple values for a single parameter in Reporting Services
  7. Creating table names that are reserved words/keywords in MS SQL Server [closed]
  8. How to order by with union in SQL?
  9. SQL to generate a list of numbers from 1 to 100
  10. How to set auto increment primary key in PostgreSQL?
  11. List columns with indexes in PostgreSQL
  12. Efficient SQL test query or validation query that will work across all (or most) databases
  13. Compare DATETIME and DATE ignoring time portion
  14. SQL – How to select a row having a column with max value [duplicate]
  15. Aggregate function in SQL WHERE-Clause
  16. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  17. Fastest way to determine if record exists
  18. I keep getting the error “relation [TABLE] does not exist”
  19. Solution to “cannot perform a DML operation inside a query”?
  20. How to concatenate many rows with same id in sql?
  21. left join turns into inner join
  22. ANSI SQL Manual
  23. SQL . The SP or function should calculate the next date for friday
  24. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  25. What does a (+) sign mean in an Oracle SQL WHERE clause? [duplicate]
  26. Temporal database design, with a twist (live vs draft rows)
  27. SQL query with distinct and sum
  28. Connecting to Oracle Database using Sql Server Integration Services
  29. How do I perform update query with subquery in Access?
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment