Swap order from an union of two results of the same table in Postgres

Question

(Using Postgres). Image we have only one table, let's call it tableA. Then we make two queries over that table and we use Union to join the result.

Something like:

query1_over_tableA
union
query2_over_tableA

so we get a result like:

result1_of_query1_over_tableA
result2_of_query1_over_tableA

Is there a way of swapping the results to have:

result2_of_query1_over_tableA
result1_of_query1_over_tableA

without inverting the order of the queries, in other words, doing something like

query2_over_tableA
union
query1_over_tableA

To clarify what I have so far:

(
    select  node.id, battery, battery_warn_level, battery_alert_level, battery_status
    from
        public.node
    join public.network on node.network_id = network.id
    where
        network.site_id = 'SOME_NETWORK_ID'
        and device_type = 3
    order by
        node_public_id asc 
)
union all 
(
    select node.id, battery, battery_warn_level, battery_alert_level, battery_status 
    from
    public.node
    join public.network on node.network_id = network.id
    where
    network.site_id = 'SOME_NETWORK_ID'
    and device_type = 1
    order by
    case
        when (node.battery_status ->> 'level'::text) = 'alert'::text then 0
        when (node.battery_status ->> 'level'::text) = 'warn'::text then 1
        else 2
    end desc,
    node.battery asc 
)

Note that the [postgres docs](https://www.postgresql.org/docs/13/queries-union.html) state "there is no guarantee that this is the order in which the rows are actually returned" so relying on the order may not be a good idea. The answers to [this question](https://stackoverflow.com/questions/31023565/how-to-have-a-custom-sort-order-for-a-union-query-in-postgres) may provide a safer method. — Brits, Mar 01 '21 at 00:09
Hi @Brits, thank you for your reply, I have added the query that I want to use to this post. Please could you give me feedback? I saw the link you send me and I think I'm close, I just want to know about the possibility to swap the two results using variables or some other trick. — catra, Mar 01 '21 at 02:22
As previously mentioned (and covered in Gordon's answer) you cannot assume that the results of `UNION ALL` will be in any particular order. In your code this means the `ORDER BY` needs to be after your closing bracket (but I would use Gordon's approach - also covered [here](https://stackoverflow.com/a/31024151/11810946)) — Brits, Mar 01 '21 at 02:46

Gordon Linoff · Answer 1 · 2021-03-01T02:29:12.390

1

union does not do what you think it does. First, it removes duplicates which usually shuffles data around in unexpected ways. Second, if you used union all, you still would not get the results in a guaranteed particular ordering.

To do that, you could use an explicit order by:

select *  -- or select all columns except ordering
from ((select . . ., 1 as ordering
       from . . .
      ) union all
      (select . . ., 2 as ordering
       from . . .
      )
     ) q
order by ordering;

You can control the ordering by changing the values for ordering.

edited Mar 01 '21 at 02:29

answered Mar 01 '21 at 00:41

Gordon Linoff

1,242,037
58
646
786

Hi @Gordon. I've added the query to this post that I have so far. The result is working fine for me. I just wanted to know if there is a way to swap the two results using variables or some other technique. Thanks – catra Mar 01 '21 at 02:25

Swap order from an union of two results of the same table in Postgres

1 Answers1