Home » SQL & PL/SQL » SQL & PL/SQL » Union and then OrderBy
Union and then OrderBy [message #194181] Thu, 21 September 2006 02:16 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
col1	curr	entered_cr	entered_dr	a
84	AUD	490851				20060730AUD000111-3	
84	AUD			490851		20060730AUD000111-2	
84	AUD	490851				20060730AUD000111-1	
84	AUD			490851		20060730AUD000111-0	
1	AUD	490851				20060730AUD000111-2	
1	GBP			200200.26	20060730AUD000111-3	
1	AUD	490851				20060730AUD000111-1	
1	AUD	490851				20060730AUD000111-0

I am getting this by these query

select col1,curr,entered_cr,entered_dr,ref1 a
from table ...
where conditions....
Union
select col1,curr,entered_cr,entered_dr,ref2 a
from table ...
where conditions...

The first query bring all rows with tag in column a is from 1 to 3 and second would return only tag suffix with '-0'

The only difference is that one column is chnaging ref1 change to ref2 , datatype and length remain same.

By ordering clause to_number(substr(a,19)) in separate query works fine , but is there any way anyone suggest to get the ordering on the top of Union Clause, ie result of the above query should be something like
col1	curr	entered_cr	entered_dr	a
84	AUD	490851				20060730AUD000111-0	
84	AUD			490851		20060730AUD000111-1	
84	AUD	490851				20060730AUD000111-2	
84	AUD			490851		20060730AUD000111-3	
1	AUD	490851				20060730AUD000111-0	
1	GBP			200200.26	20060730AUD000111-1	
1	AUD	490851				20060730AUD000111-2	
1	AUD			490851		20060730AUD000111-3


Can anyone suggest what goes wrong in passing the order by clause to get the desigred result.

thanks in advance

Re: Union and then OrderBy [message #194187 is a reply to message #194181] Thu, 21 September 2006 02:29 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Try this

SELECT col1,curr,entered_cr,entered_dr,a
FROM
(
SELECT col1,curr,entered_cr,entered_dr,ref1 a
FROM table ...
WHERE conditions....
UNION
SELECT col1,curr,entered_cr,entered_dr,ref2 a
FROM table ...
WHERE conditions...
)
ORDER BY to_number(substr(a,19));

Sandy
Re: Union and then OrderBy [message #194192 is a reply to message #194187] Thu, 21 September 2006 02:40 Go to previous message
sanjit
Messages: 65
Registered: November 2001
Member
thanks buddy
it works
Previous Topic: Regarding query
Next Topic: distinct *
Goto Forum:
  


Current Time: Thu Dec 08 22:20:08 CST 2016

Total time taken to generate the page: 0.08463 seconds