Oracle: Order by Union returning ORA-00933: SQL command not properly ended

Question

I have an issue with using Oracle's union and order by clauses together.

I have two complex queries (with sub queries in them) having an order by clause for each of them. I need to union the output of both and return the result. When I run it, I am getting the error ORA-00933: SQL command not properly ended.

But it works when I comment out the order by clauses in both of them.

To test this, I created a simple query as simple as shown below

select * from employee where employee_id=2 order by name union select * from employee where employee_id=3 order by name;

Even this gave the same error when ran with order by clauses but runs well when I commentout the order by clauses.

I tried searching forums, but I could not get solution for the exact problem. I found one at ORACLE Query with ORDER BY and UNION but As my queries are already too complecated because of subqueries and joins between too many tables, I dont want to implement this.

Can someone help me on fixing the root cause of the issue.

You can only have one order by at the very end of any union of sets. — WoMo
– WoMo, Commented Apr 18, 2014 at 15:01

Eng. Samer T · Accepted Answer · 2014-04-18 15:17:26Z

try this code:

select e1.name name /* e1.* */ from employee e1 where employee_id = 2 union select e2.name name /* e2.* */ from employee e2 where employee_id = 3 order by name;

if you want to order the result of first query then to order the result the second query so you can do like this:

select 1 query, e1.name name /* e1.* */ from employee e1 where employee_id = 2 union select 2 query, e2.name name /* e2.* */ from employee e2 where employee_id = 3 order by query, name;

I already tried this. This did not work. it gives Error: ORA-00904: "column name used in order by clause": invalid identifier
Thanks very much for the quick help. I modified your fist suggestion a little bit and it worked for me. I just changed the column alias to name1 both in the select and in the order by clauses.

WoMo · Accepted Answer · 2014-04-18 15:03:06Z

You can have only one ORDER BY when combining multiple queries, on the last statement. The ORDER BY clause acts on the entire set.

See the Oracle Documentation:

You cannot specify the order_by_clause in the subquery of these operators.

Mostafa Vatanpour · Accepted Answer · 2022-08-20 13:40:30Z

If you want order by in each query you must wrap it in other select as a subquery:

select * from (select * from employee where employee_id=2 order by name) union select * from (select * from employee where employee_id=3 order by name);

Collectives™ on Stack Overflow

Oracle: Order by Union returning ORA-00933: SQL command not properly ended

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related