Re: Sql Order by question

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 Received on Fri Oct 19 2007 - 16:50:26 CDT