Home » SQL & PL/SQL » SQL & PL/SQL » UNION ALL with ORDER BY
UNION ALL with ORDER BY [message #390782] Mon, 09 March 2009 14:58 Go to next message
manju_cy
Messages: 10
Registered: February 2009
Junior Member
I am not able to use ORDER BY with UNION ALL query. Please suggest why is it so and how to use.
Re: UNION ALL with ORDER BY [message #390783 is a reply to message #390782] Mon, 09 March 2009 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>I am not able to use ORDER BY with UNION ALL query.

Show us what you did & how Oracle responded.


Re: UNION ALL with ORDER BY [message #390785 is a reply to message #390783] Mon, 09 March 2009 15:27 Go to previous messageGo to next message
manju_cy
Messages: 10
Registered: February 2009
Junior Member
Lets say I am doing as shown below

select t1.empno, t1.ename, t1.deptno, t1.hiredate, count(*)
from scott.emp t1
where t1.sal > 1000
group by t1.empno, t1.ename, t1.deptno, t1.hiredate
order by t1.hiredate
union all
select t1.empno, t1.ename, t1.deptno, t1.hiredate, count(*)
from scott.emp t1
where t1.sal > 2000
group by t1.empno, t1.ename, t1.deptno, t1.hiredate
order by t1.hiredate
Re: UNION ALL with ORDER BY [message #390786 is a reply to message #390782] Mon, 09 March 2009 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Let's say you actually read & FOLLOW what has been requested.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Show us what you did & how Oracle responded.

Re: UNION ALL with ORDER BY [message #390787 is a reply to message #390785] Mon, 09 March 2009 15:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There can be only one ORDER BY in query that sorts the whole result set.

Regards
Michel
Re: UNION ALL with ORDER BY [message #390788 is a reply to message #390786] Mon, 09 March 2009 15:38 Go to previous messageGo to next message
manju_cy
Messages: 10
Registered: February 2009
Junior Member
Thanks I dont have time to give you all the details I mean test data, expected results..... I will find the solution with some other sites. Thanks aanyway.
Re: UNION ALL with ORDER BY [message #390791 is a reply to message #390787] Mon, 09 March 2009 15:41 Go to previous messageGo to next message
manju_cy
Messages: 10
Registered: February 2009
Junior Member
only once? where? after the first uery or the second query?

Lets say if the order by is on different columns?

select t1.empno, t1.ename, t1.deptno, t1.hiredate, count(*)
from scott.emp t1
where t1.sal > 1000
group by t1.empno, t1.ename, t1.deptno, t1.hiredate
order by t1.hiredate
union all
select t1.empno, t1.ename, t1.deptno, t1.hiredate, count(*)
from scott.emp t1
where t1.sal > 2000
group by t1.empno, t1.ename, t1.deptno, t1.hiredate
order by t1.deptno
Re: UNION ALL with ORDER BY [message #390792 is a reply to message #390791] Mon, 09 March 2009 15:45 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you, actually, try to run your code?
SQL> select ename, sal from emp order by sal
  2  union all
  3  select ename, sal from emp order by ename;
union all
*
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL>


SQL> select ename, sal from emp
  2  union all
  3  select ename, sal from emp order by ename;

ENAME             SAL
---------- ----------
ADAMS            1100
ADAMS            1100
ALLEN            1600
ALLEN            1600
...
Re: UNION ALL with ORDER BY [message #390794 is a reply to message #390792] Mon, 09 March 2009 15:48 Go to previous messageGo to next message
manju_cy
Messages: 10
Registered: February 2009
Junior Member
Thanks a lot this helped me..
Re: UNION ALL with ORDER BY [message #390796 is a reply to message #390787] Mon, 09 March 2009 15:49 Go to previous messageGo to next message
manju_cy
Messages: 10
Registered: February 2009
Junior Member
Thanks Michel.
Re: UNION ALL with ORDER BY [message #390818 is a reply to message #390782] Mon, 09 March 2009 23:12 Go to previous message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

Thanks I dont have time to give you all the details I mean test data, expected results..... I will find the solution with some other sites. Thanks aanyway.



Strange Demand !!! but still got the solution

Regards,
Ashoka BL

Previous Topic: Issues in creating a trigger which fires during user modification
Next Topic: Date difference in days
Goto Forum:
  


Current Time: Fri Dec 02 16:49:54 CST 2016

Total time taken to generate the page: 0.18755 seconds