Union

[malru]

I wants to write a query such that the first 4 rows should be sorted in ASC order & the remainig rows should be sorted in DESC order.

I written this query to sort the first 4 rows,

SQL> select id from (select id from (select id,rownum v from emp)v1 where v1.v b
etween 1 and 4 order by id asc);

ID
----------
9
10
11
12


I written this query to sort the remaining rows,

SQL> select id from (select id from (select id,rownum v from emp)v1 where v1.v b
etween 5 and 7 order by id desc);

ID
----------
15
14
13

Then i combined the result of these queries using UNION to get my required result,i.e., the output should like this,

ID
----------
9
10
11
12
15
14
13

But i got the output as follows,

SQL> select id from (select id from (select id,rownum v from emp)v1 where v1.v b
etween 1 and 4 order by id asc)
2 union
3 select id from (select id from (select id,rownum v from emp)v1 where v1.v b
etween 5 and 7 order by id desc)
4 /

ID
----------
9
10
11
12
13
14
15

7 rows selected.

Eventhough the UNION works for simple query as follows,

SQL> select id from emp1;

ID
----------
10
15

SQL> select id from emp2 order by id desc;

ID
----------
7
5

SQL> select id from emp1
2 union
3 select id from emp2 order by id desc;

ID
----------
15
10
7
5

Why it's not working,can anyone tell me what is the error in this & what i've to do get the output like,

ID
----------
9
10
11
12
15
14
13

Thanks,
Malru

[DaPi]

Why it's not working - well Oracle "reserves the right" to order sets as it sees best unless there is an explicit Order By for the result set. As Union eliminates duplicates, the two sets could be resorted to perform this (it looks like that happens in your first case).

It's not pretty, but you'll have to code it something like:

select empno from
(
 select empno, 1 nset, rownum num
 from (select empno from (select empno, rownum v from emp order by empno) v1 
       where v1.v between 1 and 4 order by empno asc)
   union
 select empno, 2 nset, rownum num
 from (select empno from (select empno, rownum v from emp order by empno) v1 
       where v1.v between 5 and 7 order by empno desc)
)
order by nset, num
/

[slimdave]

Your powers are weak, old man.

select id
from (select id, rownum rn from emp)
order by case when rn < 5 then rn else 1000000000-rn end

[DaPi]

Well slim, I was working on the assumption that this is a simplified example of a more complex case. I was looking for a general solution, which BTW I haven't found - spot the bug!