Union and then OrderBy [message #194181] |
Thu, 21 September 2006 02:16  |
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   |
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
|
|
|
|