PostgreSQL syntax error at or near 'union'

Question

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>
      ,<column3>
From <Table1> 
<Some joins in there>
where <conditions>
order by <column2>

union

select <column2>
      ,<column3>
      ,<column4>
from <Table2>
<Some more joins here>
where <conditions>
order by <column2>

This is the Error I receive

ERROR: Syntax error at or near 'union'

Padagomez · Accepted Answer · 2014-12-04T22:27:20.220

34

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.

edited Dec 04 '14 at 22:27

answered Jun 03 '14 at 16:18

Padagomez

1,114
5
14
35

2

Good answer, I would also suggest that you specify your column names rather than just using `*` – crthompson Jun 03 '14 at 16:19
2

And you can't have 2 columns in one part of the UNION and 3 columns in another. It will give you a different error. – ypercubeᵀᴹ Jun 03 '14 at 17:10
I see, I should fix that. – Padagomez Jun 03 '14 at 17:28

score 30 · Answer 2 · answered Jun 03 '14 at 20:47

Short answer: (SELECT... ORDER BY..) UNION (SELECT .. ORDER BY...) does work.

See the documentation about UNION:

UNION Clause

The UNION clause has this general form:

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

score 5 · Answer 3 · answered Nov 25 '21 at 12:05

place each query inside a bracket

(Select <column1>
      ,<column2>
      ,<column3>
From <Table1> 
<Some joins in there>
where <conditions>
order by <column2> )

union

(select <column2>
      ,<column3>
      ,<column4>
from <Table2>
<Some more joins here>
where <conditions>
order by <column2>)

PostgreSQL syntax error at or near 'union'

3 Answers3

Linked