Hello,

Can somebody help me resolve following order by problem, I am
running out of ideas.

I have following temp table with following data

CREATE TABLE TEMP
(COL1 NUMBER,
COL2 NUMBER
)

Data

COL1 COL2

1 1
1 2
1 3
3 4
2 5
1 6
1 7
2 8
2 9
3 10

I want the returned data to be ordered by as

COL1 COL2

1 1
1 2
1 3
1 6
1 7
3 4
3 10
2 5
2 8
2 9

Is this possible using sql query?

Thanks

On Oct 19, 4:35 pm, m&m <milin.sh... (AT) gmail (DOT) com> wrote:
Certainly, but it involves case and the noprint option to SQL*Plus:

SQL> create table temp(col1 number, col2 number);

Table created.

SQL> insert all
2 into temp
3 values(1,1)
4 into temp
5 values(1,2)
6 into temp
7 values(1,3)
8 into temp
9 values(3,4)
10 into temp
11 values(2,5)
12 into temp
13 values(1,6)
14 into temp
15 values(1,7)
16 into temp
17 values(2,8)
18 into temp
19 values(2,9)
20 into temp
21 values(3,10)
22 select * from dual;

10 rows created.

SQL> commit;

Commit complete.

SQL> column sortord noprint
SQL> select col1, col2,
2 case when col1 = 1 then 1 when col1=2 then 3 when col1= 3 then 2
end sortord
3 from temp
4 order by sortord, col2
5 /

COL1 COL2
---------- ----------
1 1
1 2
1 3
1 6
1 7
3 4
3 10
2 5
2 8
2 9

10 rows selected.

SQL>

Witih this 'solution' you need to know your values. Possibly someone
else can devise a clever alternative.


David Fitzjarrell

On Fri, 19 Oct 2007 14:50:26 -0700, "fitzjarrell (AT) cox (DOT) net"
<fitzjarrell (AT) cox (DOT) net> wrote:

If those really are the numbers (seems so unlikely)

SELECT * FROM temp
ORDER BY col1 + 2 - (2 * Mod(col1,2));

1 1
1 2
1 3
1 6
1 7
3 4
3 10
2 5
2 8
2 9

Using the minus mod gives the greater value to even numbers.
Arch

On Oct 19, 7:30 pm, Arch <send... (AT) spam (DOT) net> wrote:

Thanks David and Arch for your time and solution. I was able to solve
my problem using Arch's solution.