On Oct 19, 7:30 pm, Arch <send..._at_spam.net> wrote:
> On Fri, 19 Oct 2007 14:50:26 -0700, "fitzjarr..._at_cox.net"
>
>
>
>
>
> <fitzjarr..._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- Hide quoted text -
>
> - Show quoted text -
Thanks David and Arch for your time and solution. I was able to solve
my problem using Arch's solution.
Received on Mon Oct 22 2007 - 15:51:49 CDT
