SQL Union All with order by and limit (Postgresql)
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);You could also order final query:
(SELECT 'a' AS colORDER BY col LIMIT 1)UNION ALL (SELECT 'b' AS colORDER 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 colORDER BY col DESC LIMIT 1)UNION ALL(SELECT 'b' AS colORDER BY col DESC LIMIT 1)ORDER BY col DESC LIMIT 1otherwise 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.
SELECT <property1>, <property2>FROM <table1> LIMIT 1UNION ALLSELECT <property1>, <property2>FROM <table2> WHERE <condition> ORDER BY <condition> LIMIT 1;