CODEWITHSUNDEEP
postgresqlsql-order-byunion
Dizz
Dizz

Reputation: 43

Order of SELECT statements using the UNION function?

How do I order the select statements using the UNION function? I want the output to be the order the select statements. EG T1 then T2 then T3.

Query:

select 'T1' as Table, sum(col1) from table1
group by 'T1'
UNION
select 'T2' as Table, sum(col2) from table2
group by 'T1'
UNION
select 'T3' as Table, sum(col3) from table3
group by 'T1'

order by 'T1', 'T2', 'T3'

EXPECTED OUTPUT:

Table Another header
T1 45
T2 90
T3 71

Upvotes: 0

Views: 34

Answers (1)

Anton Grig
Anton Grig

Reputation: 1719

You can add an auxiliary column for sorting as follows:

with using CTE

With CTE As (
select 0 as Sort, 'T1' as "Table", sum(col1) as "Sum" from table1
UNION
select 1 as Sort, 'T2' as "Table", sum(col2) as "Sum" from table2
UNION
select 2 as Sort, 'T3' as "Table", sum(col3) as "Sum" from table3
)
Select "Table", "Sum"
From CTE
Order by Sort

It should work also using a subquery, since subquery scan will be applied to the sorted input.

Select "Table", "Sum"
From (
select 0 as Sort, 'T1' as "Table", sum(col1) as "Sum" from table1
UNION
select 1 as Sort, 'T2' as "Table", sum(col2) as "Sum" from table2
UNION
select 2 as Sort, 'T3' as "Table", sum(col3) as "Sum" from table3
Order by Sort   
) As T

Upvotes: 2

Related Questions