Re: Sql Order by question

On Fri, 19 Oct 2007 14:50:26 -0700, "fitzjarrell_at_cox.net" <fitzjarrell_at_cox.net> wrote:

>On Oct 19, 4:35 pm, m&m <milin.sh..._at_gmail.com> wrote:
>> 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
>
>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

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 Received on Fri Oct 19 2007 - 19:30:40 CDT