SQL Question

[lesstjm]

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?

[jgmagnus]

take off the order by clause and see what you get.

- Magnus

[hptse]

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

[culonbu]

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
/

[culonbu]

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
/

[hptse]

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.

[nansbrain]

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

[PSoni]

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

[chrisrlong]

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