-
I am trying to create a query that will return two specific records first and then follow them by the rest of the records that match the query. For example:
select acno from rcb_acct
where acno in ('555555666666666','333333333333333')
and lgon_pref = 'MYN'
order by acno
union
select acno from rcb_acct
where lgon_pref = 'MYN'
order by acno
/
I want the first two records that match my first select statement and then the rest that match the second selec statement. The problem I am having with the above query is that it doesn't like the order by clause. I need the results of each query sorted seperately. Is there a way I could run two queries and concatenate the results together?
-
try this
take off the order by clause and see what you get.
- Magnus
-
use DECODE
you can try this
select acno from rcb_acct
where lgon_pref = 'MYN'
order by
decode(acno ,
'555555666666666','1'
'333333333333333','2',acno) ;
assuming acno is always greater than '1','2' so that default value from decode is the acno and will be ordered after '1' and '2'.
OR
if you want to sort by two groups of union, try this
select acno,1 ordering from rcb_acct
where acno in ('555555666666666','333333333333333')
and lgon_pref = 'MYN'
union
select acno,2 ordering from rcb_acct
where lgon_pref = 'MYN'
order by ordering, acno
hptse
-
try this
select acno , 1 as ord
from rcb_acct
where acno in ('555555666666666','333333333333333')
and lgon_pref = 'MYN'
order by acno , ord
union
select acno, 2 as ord
from rcb_acct
where lgon_pref = 'MYN'
order by acno , ord
/
-
sorry wrong order by statement
the sql should read
select acno , 1 as ord
from rcb_acct
where acno in ('555555666666666','333333333333333')
and lgon_pref = 'MYN'
order by ord, acno
union
select acno, 2 as ord
from rcb_acct
where lgon_pref = 'MYN'
order by ord, acno
/
-
ORDER BY cannot be used in the sql statement of a UNION.
It can be used only after the UNION, hence only one ORDER BY is allowed.
hptse
-
Complicating a little too much...
I hope this will solve your problem.
SELECT
DECODE(acno,'555555666666666',1,'333333333333333',2,3) sort_order, acno
FROM rcb_acct
WHERE lgon_pref = 'MYN'
ORDER BY sort_order, acno
Cheers,
Nanda
-
Hi,
select acno from rcb_acct
where acno in ('555555666666666','333333333333333')
and lgon_pref = 'MYN'
-- removed order by clause from here; now it will work
union
select acno from rcb_acct
where lgon_pref = 'MYN'
order by acno
Pls. check.
P. Soni
-
I just noticed this is the same query that I solved under a different title. Please try to keep the questions consolidated into a single post.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|