In the following query I get syntax error:
SELECT <property1>, <property2>
FROM <table1>
ORDER BY <condition> LIMIT 1
UNION ALL
SELECT <property1>, <property2>
FROM <table2>
WHERE <condition> ORDER BY <condition> LIMIT 1;
syntax error at or near "UNION" LINE 4: UNION ALL
Each of the SELECT
stand alone executes fine. My guess is about the ORDER BY... LIMIT 1
maybe?
The UNION ALL operator can use the ORDER BY clause to order the results of the query in SQL Server (Transact-SQL).
As the following query shows, when you include an ORDER BY clause, it must follow the final SELECT statement and use an integer, not an identifier, to refer to the ordering column.
The PostgreSQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements.
Wrap each query with ()
:
(SELECT <property1>, <property2>
FROM <table1>
ORDER BY <condition> LIMIT 1)
UNION ALL
(SELECT <property1>, <property2>
FROM <table2>
WHERE <condition> ORDER BY <condition> LIMIT 1);
SqlFiddleDemo
You could also order final query:
(SELECT 'a' AS col
ORDER BY col LIMIT 1)
UNION ALL
(SELECT 'b' AS col
ORDER BY col LIMIT 1)
ORDER BY col DESC
The first answer of @lad2025 is correct,
but the generalization just under is not correct because must be the whole condition, desc clause included.
This is the correct code :
(SELECT 'a' AS col
ORDER BY col DESC LIMIT 1)
UNION ALL
(SELECT 'b' AS col
ORDER BY col DESC LIMIT 1)
ORDER BY col DESC LIMIT 1
otherwise you select only le highest of the two lowest col of select 1 and select 2 (if any)
(and not the highest of all the cols)
and you must not forget the LIMIT 1 at the end too.
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