How to have a custom sort order for a union query in Postgres

Question

With a query like this (simplified for clarity):

SELECT 'East' AS name, *
FROM events 
WHERE event_timestamp BETWEEN '2015-06-14 06:15:00' AND '2015-06-21 06:15:00' 

UNION

SELECT 'West' AS name, *
FROM events 
WHERE event_timestamp BETWEEN '2015-06-14 06:15:00' AND '2015-06-21 06:15:00'

UNION

SELECT 'Both' AS name, *
FROM events 
WHERE event_timestamp BETWEEN '2015-06-14 06:15:00' AND '2015-06-21 06:15:00'

I want to customise the order of the resulting rows. Something like:

ORDER BY name='East', name='West', name='Both'

Or

ORDER BY 
    CASE
        WHEN name='East' THEN 1 
        WHEN name='West' THEN 2
        WHEN name='Both' THEN 3
        ELSE 4
    END;

However, Postgres complains with:

ERROR:  invalid UNION/INTERSECT/EXCEPT ORDER BY clause
DETAIL:  Only result column names can be used, not expressions or functions.
HINT:  Add the expression/function to every SELECT, or move the UNION into a FROM clause.

Do I have any alternative?

score 41 · Accepted Answer · answered Jun 24 '15 at 10:26

Wrap it in a derived table (which is what "HINT: .... or move the UNION into a FROM clause" is suggesting)

select *
from (
  ... your union goes here ... 
) t
order by
    CASE
        WHEN name='East' THEN 1 
        WHEN name='West' THEN 2
        WHEN name='Both' THEN 3
        ELSE 4
    END;

score 17 · Answer 2 · answered Jun 24 '15 at 10:27

17

I'd add an extra column showing the desired ordering, then use ordinal column positions in the ORDER BY, e.g.

SELECT 1, 'East' AS name, *
...
UNION ALL
SELECT 2, 'West' AS name, *
...
ORDER BY 1

Note that you probably also want UNION ALL since your added columns ensure that every set in the union must be distinct anyway.

answered Jun 24 '15 at 10:27

Craig Ringer

307,061
76
688
778

How would you use this inside a function that must only return a single value? – pir Jul 30 '20 at 18:44
1

@pir Wrap it in an outer query that `SELECT`s just the value-column, using `FROM (SELECT ...)` – Craig Ringer Aug 02 '20 at 23:56

score 2 · Answer 3 · answered Feb 07 '16 at 21:26

2

By adding an extra column for ordering purpose, however it makes the UNION clause to work exactly as a UNION ALL (it does not eliminate duplicate rows from the result).

answered Feb 07 '16 at 21:26

Pankus

1,277
1
12
17

it is a great note, but it should be **a comment** on this answer: https://stackoverflow.com/a/31024172/1115187, not a new answer – maxkoryukov Jan 30 '23 at 19:39

How to have a custom sort order for a union query in Postgres

3 Answers3

Linked