OraFAQ Forum: SQL & PL/SQL » UNION ALL with ORDER BY

Home » SQL & PL/SQL » SQL & PL/SQL » UNION ALL with ORDER BY

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNION ALL with ORDER BY [message #390782]

Mon, 09 March 2009 14:58

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.

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390783 is a reply to message #390782]

Mon, 09 March 2009 15:03

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390785 is a reply to message #390783]

Mon, 09 March 2009 15:27

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

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390786 is a reply to message #390782]

Mon, 09 March 2009 15:30

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390787 is a reply to message #390785]

Mon, 09 March 2009 15:38

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There can be only one ORDER BY in query that sorts the whole result set.

Regards
Michel

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390788 is a reply to message #390786]

Mon, 09 March 2009 15:38

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.

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390791 is a reply to message #390787]

Mon, 09 March 2009 15:41

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

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390792 is a reply to message #390791]

Mon, 09 March 2009 15:45

Littlefoot
Messages: 21823
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
...

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390794 is a reply to message #390792]

Mon, 09 March 2009 15:48

manju_cy
Messages: 10
Registered: February 2009

Junior Member

Thanks a lot this helped me..

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390796 is a reply to message #390787]

Mon, 09 March 2009 15:49

manju_cy
Messages: 10
Registered: February 2009

Junior Member

Thanks Michel.

Report message to a moderator

Re: UNION ALL with ORDER BY [message #390818 is a reply to message #390782]

Mon, 09 March 2009 23:12

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 18:16:14 CST 2024