How to combine SQL scalars correctly?-CodePudding

Having a few queries that return only one row and one column, how would you combine them in a single result set?

Example:

SELECT TOP 1 [col1] 
FROM [table] 
WHERE con1 = true 
ORDER BY [col1] DESC

SELECT TOP 1 [col1] 
FROM [table] 
WHERE con2 = true 
ORDER BY [col1] DESC

I could do this:

SELECT TOP 1
    (SELECT TOP 1 [col1] FROM [table] 
     WHERE con1 = true 
     ORDER BY [col1] DESC) AS 'Res1', 
    (SELECT TOP 1 [col1] FROM [table] 
     WHERE con2 = true 
     ORDER BY [col1] DESC) AS 'Res2'
FROM [table]

However it is not necessary that the results are combined as columns, the results in rows would be also ok or even as concatenation if that makes any sense, but maybe columns is already the better way.

I use SQL Server

CodePudding user response：

As I mentioned in the comments, there's no need for the FROM in the outer query; just have your 2 subqueries:

SELECT (SELECT TOP (1)
               col1
        FROM dbo.[table]
        WHERE con1 = 1 --Boolean data types don't exists in SQL Server; I assume this is a bit
        ORDER BY col1 DESC) AS Res1, --Don't use string literals for aliases 
       (SELECT TOP (1)
               col1
        FROM dbo.[table] 
        WHERE con2 = 1 --Boolean data types don't exists in SQL Server; I assume this is a bit
        ORDER BY col1 DESC) AS Res2; --Don't use string literals for aliases

CodePudding user response：

Use UNION operator if the output fields are same for both queries

Query 1
Union
Query 2