Content-based Order by

drop table lem.laender;

create table lem.laender
    (
    id      number(5)       not null,
    name    varchar2(100)   unique,
    lang    number(2)       not null
    );
    
insert into lem.laender values( 1,'Italien'         ,1);
insert into lem.laender values( 2,'Frankreich'      ,1);
insert into lem.laender values( 3,'Amerika'         ,1);
insert into lem.laender values( 4,'Russland'        ,1);
insert into lem.laender values( 5,'Deutschland'     ,1);
insert into lem.laender values( 6,'Österreich'      ,1);
insert into lem.laender values( 7,'Lichtenstein'    ,1);
insert into lem.laender values( 8,'Belgien'         ,1);
insert into lem.laender values( 9,'Holland'         ,1);
insert into lem.laender values(10,'Schweden'        ,1);
insert into lem.laender values(11,'Norwegen'        ,1);
insert into lem.laender values(12,'Schweiz'         ,1);
insert into lem.laender values(13,'----------------',1);

commit;    
    
-- Select without any order by Statements:
    select  name 
      from  lem.laender;
      
-- Select with simple order by Statements:    
    select  name 
      from  lem.laender
      order by name asc;

-- Select with Content-based order by Statements:
    select  name 
      from  lem.laender 
      order by  case 
                    when id in (12)         then 0 -- MyPosition
                    when id in (7)          then 1 -- LOCAL
                    when id in (13)         then 2 -- Line
                    when id in (1,2,5,6)    then 3 -- Nachbarn
                    when id in (8,9,10,11)  then 4 -- EU
                    else 9
                end, 
                name asc;