Oracle sql sort week days by current day

Question

I am trying to sort the days based on the order: Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday. I am trying using case:

select day,
 CASE day
  WHEN  1 THEN 1
  WHEN  2 THEN 2
  WHEN  3 THEN 3
  WHEN  4 THEN 4
  WHEN  5 THEN 5
  WHEN  6 THEN 6
  WHEN  7 THEN 7
  else 0
  END as day_nr
from week where day in (1,2,3,4,5,6,7)
order by day_nr asc

This is ok when I select all the days of the week. But if I want only for the day 1,5,6 the ordering is not correct. Gets the first day -Monday. How to proceed?

Answer 1

In Oracle day 1 is Sunday by default.

SELECT * FROM
(
 SELECT trunc(sysdate) + LEVEL-1 my_dt
      , TO_CHAR(trunc(sysdate) + LEVEL-1, 'DY') Wk_Day
      , TO_CHAR(trunc(sysdate) + LEVEL-2, 'D' ) Day#
   FROM dual 
 CONNECT BY LEVEL <= 10
 )
WHERE Day# IN (1,5,6)
ORDER BY my_dt, Day#
/

MY_DT    WK_DAY    DAY#
------------------------
5/10/2013    FRI    5
5/11/2013    SAT    6
5/13/2013    MON    1
5/17/2013    FRI    5
5/18/2013    SAT    6

Answer 2

If you're trying to sort a set of dates by day of the week, with Saturday being the first, then consider ordering by a modified date:

create table t1(my_date date);
insert into t1
select trunc(sysdate)+rownum
from dual
connect by level <= 20

select
  my_date,
  to_char(my_date,'Day'),
  to_char(my_date,'D')
from
  t1
order by
  to_char(my_date + 1,'D');

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

The downside is that it's not very intuitive, so add a code comment if you use this method.

Edit: Where you have a list of numbers, order by a case statement with either a list conversion:

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

... or the more compact, but not as intuitive:

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

order by case