SQL Union All with order by and limit (Postgresql)

sql postgresql union union-all
michael picture michael · May 20, 2016 · Viewed 12.3k times · Source

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

Lukasz Szozda picture Lukasz Szozda · May 20, 2016

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

Related questions

Combine multiple SELECT statements

I've used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas: select result from foo.table limit 1; select result from bar.table limit 1; select result from …

Read More
Combine two SELECT queries in PostgreSQL

I would like to combine two select queries with UNION. How can I use the result from the first SELECT in the second SELECT? (SELECT carto_id_key FROM table1 WHERE tag_id = 16) UNION (SELECT * FROM table2 WHERE carto_id_…

Read More
Combine two sql select queries (in postgres) with LIMIT statement

I've got a table and I want a query that returns the last 10 records created plus the record who's id is x. I'm trying to do - SELECT * FROM catalog_productimage ORDER BY date_modified LIMIT 10 UNION SELECT * FROM catalog_…

Read More