CODEWITHSUNDEEP
sqlgroup-bysql-order-byamazon-redshiftunion
AndyP
AndyP

Reputation: 607

Using group/order by with union clause in sql query

I have four sql queries which gave me same columns so I am trying to combine them using UNION clause. Below is what I have tried but it gives me an error:

select clientid, 
'Test1' as client_name,
client_timestamp,
sum(client_counts) as count,
processIds as contracts
from output_1
group by 1,2,3,5
order by 1

UNION

select clientid, 
'Test2' as client_name,
client_timestamp,
sum(client_counts) as count,
'' as contracts
from output_2
group by 1,2,3,5
order by 1

UNION

select clientid, 
'Test3' as client_name,
client_timestamp,
sum(kite_count) as count,
process_metric as contracts
from output_3
group by 1,2,3,5
order by 1

UNION

select clientid, 
'Test4' as client_name,
execution_client_ts as client_timestamp,
sum(kite_count) as count,
process_data as contracts
from output_4
group by 1,2,3,5
order by 1

Error I get is "Invalid Syntax" around UNION line. Anything wrong I am doing here?

Upvotes: 0

Views: 1051

Answers (1)

GMB
GMB

Reputation: 222402

A union query may only have one order by clause.

If you are satisfied with ordering the whole resultset, you can remove all order by clauses and just keep the very last one, at the end of the query. It applies to the entire dataset that union generates.

Note that your UNIONs are equivalent to UNION ALLs - because the client name is different in each member - and should be phrased as such.

If, on the other hand, you want to order reach sub-result, then this is different. Basically you need a flag in each member, that can then be used to identify each group. The client name might be a good pick, so:

order by client_name, client_id

Upvotes: 3

Related Questions