SQL syntax error at or near 'union'

sql postgresql sql-order-by union
Padagomez picture Padagomez · Jun 3, 2014 · Viewed 14.6k times · Source

I have a small query, and a union to put another small query next to it. However, the union has a syntax error in it. 

Select <column1>
      ,<column2>
From <Table1> 
<Some joins in there>
Where <conditions>
group by <column2>
order by <column2>

union

select <column2>
      ,<column3>
      ,<column4>
From <Table2>
<Some more joins here>
Where <conditions>
group by <column2>
order by <column2>

This is the Error I receive

ERROR: Syntax error at or near 'union'

Answer

Padagomez picture Padagomez · Jun 3, 2014

I see what was wrong. You have to place the order by at the end of the query, and only at the end. It gave me an error because it thought the query had eneded.

Select <column1>
      ,<column2>
      ,<aggregate column3>
From <Table1> 
<Some joins in there>
Where <conditions>
group by <column2>, <column1>

union

select <column2>
      ,<column3>
      ,<aggregate column4>
From <Table2>
<Some more joins here>
Where <conditions>
group by <column2>, <column3>
order by <column2>

That did the trick.

Related questions

PostgreSQL DISTINCT ON with different ORDER BY

I want to run this query: SELECT DISTINCT ON (address_id) purchases.address_id, purchases.* FROM purchases WHERE purchases.product_id = 1 ORDER BY purchases.purchased_at DESC But I get this error: PG::Error: ERROR: SELECT DISTINCT ON expressions must …

Read More
sql ORDER BY multiple values in specific order?

Ok I have a table with a indexed key and a non indexed field. I need to find all records with a certain value and return the row. I would like to know if I can order by multiple values. …

Read More
ORDER BY the IN value list

I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sorted list of values to the IN construct in the WHERE clause: SELECT * FROM comments WHERE (comments.id IN (1,3,2,4)); This returns comments in …

Read More