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?
Union is a type of operator in MySQL. We can use ORDER BY with this to filter records. Use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. Let us see an example.
To sort a UNION in a view or in-line function, you must create a view on the query containing the UNION and then sort the view. You can also embed the UNION query in a FROM clause of a query and then sort the result. You can combine multiple SELECT statements using UNION to obtain complex results.
The PostgreSQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With