michael michael - 6 months ago 38
SQL Question

SQL Union All with order by and limit (Postgresql)

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?

Answer

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
Comments