I have two queries that give me back a single entry. How can I select both of these as on table?
query1: Select max([column3]) from [table1] => 42
query2: Select Top 1 [column1] from [table1] => 'test'
I want a resultset like this
| result1 | result2 |
|---|---|
| 42 | ‘test’ |
But how to do it correctly? Can I maybe select from nowhere somehow?
>Solution :
You could use ROW_NUMBER, twice:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY column3 DESC) rn1,
ROW_NUMBER() OVER (ORDER BY some_col) rn2
FROM table1
)
SELECT MAX(CASE WHEN rn1 = 1 THEN column3 END) AS result1,
MAX(CASE WHEN rn2 = 1 THEN column1 END) AS result2
FROM cte;
Note that I assume there exists a column some_col which you intend to use for choosing the column1 value.