Geek Answers Handbook

SQL syntax error in or next to 'union'

I have a small query and a join to add another small query to it. However, there is a syntax error in the union.

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 get

 ERROR: Syntax error at or near 'union'
+8
sql sql-order-by postgresql union
source share
2 answers

I see what happened. You must place an order at the end of the request and only at the end. This gave me an error because I thought the request had entered.

 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>

This is a trick.

+11
source share

Short answer: (SELECT... ORDER BY..) UNION (SELECT .. ORDER BY...) works.

See the UNION documentation :

UNION offer

The UNION proposal has this general view:

select_statement UNION [ALL | DISTINCT] select_statement

select_statement - any SELECT statement without ORDER BY, LIMIT, FOR NO KEY UPDATES, ABOUT UPDATES, FOR SHARES, OR FOR OPERATION OF KEY SHARES. ( ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses . Without parentheses, these positions will be applied to the result of the UNION, and not to its right input Expression.)

+10
source share

More articles:


All Articles