Re: Modify the order sequence

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 7 Sep 2005 17:23:35 +0200
Message-ID: <431f05f4$0$17647$636a15ce@news.free.fr>

<kevin.davis_at_kevincdavis.net> a écrit dans le message de news: 1126105791.181924.310290_at_f14g2000cwb.googlegroups.com...
| Hello,
|
| I have a question with regards to the order seqeunce.. I have data that
| is displayed by id numbers. The id numbers are 1,2,3,4,5,6,8,9,10 .. Is
| there a sql statement that will allow the id to sort like this:
| 1,2,8,3,4,5,6,9,10 ?
|
| Thanks!
|
| Kevin
|

If there no mathematical logic in your order, you can use decode if the criteria is not too complex:

select ... order by decode(1,1, 2,2, 8,3, 3,4, 4,5, ...);

else create a function that returns a number and use it in your order clause:

create or replace my_order_function (id in number) return number is
  ret number;
begin
  case id

    when 1 then ret := 1;
    when 2 then ret := 2;
    when 8 then ret := 3;
    when 3 then ret := 4;
    when 4 then ret := 5;

    ...
  end case;
  return ret;
end;
/

select ... order by my_order_function(id);

Regards
Michel Cadot Received on Wed Sep 07 2005 - 10:23:35 CDT