Oracle sql sort week days by current day -

i trying sort days based on order: saturday, sunday, monday, tuesday, wednesday, thursday, friday. trying using case:

select day,  case day   when  1 1   when  2 2   when  3 3   when  4 4   when  5 5   when  6 6   when  7 7   else 0   end day_nr week day in (1,2,3,4,5,6,7) order day_nr asc 

this ok when select days of week. if want day 1,5,6 ordering not correct. gets first day -monday. how proceed?

if you're trying sort set of dates day of week, saturday being first, consider ordering modified date:

create table t1(my_date date); insert t1 select trunc(sysdate)+rownum dual connect level <= 20  select   my_date,   to_char(my_date,'day'),   to_char(my_date,'d')   t1 order   to_char(my_date + 1,'d'); 

http://sqlfiddle.com/#!4/5940b/3

the downside it's not intuitive, add code comment if use method.

edit: have list of numbers, order case statement either list conversion:

case day   when 1 3   when 2 4   when 3 5   when 4 6   when 5 7   when 6 1 -- saturday   when 7 2 end 

... or more compact, not intuitive:

case   when day <= 5 day + 2   else               day - 5 end  order case