Sort by day of the week from Monday to Sunday

Question

If I write

select ename, to_char(hiredate,'fmDay') as "Day" order by "Day";

Then it sorts the result based on Day like; from Friday, then Monday and last Wednesday, like sorting by characters.

But I want to sort it by day of the week; from Monday to Sunday.

Answer 1

SELECT
     *
FROM
     classes
ORDER BY 
     CASE
          WHEN Day = 'Sunday' THEN 1
          WHEN Day = 'Monday' THEN 2
          WHEN Day = 'Tuesday' THEN 3
          WHEN Day = 'Wednesday' THEN 4
          WHEN Day = 'Thursday' THEN 5
          WHEN Day = 'Friday' THEN 6
          WHEN Day = 'Saturday' THEN 7
     END ASC

Assuming that user has a table called classes in that table user has class_id (primary key), class name, Day.

Answer 2

You're getting it in the order you are because you're ordering by a string (and this wouldn't work because you're not selecting from anything).

You could order by the format model used to create the day of the week in numeric form, D, but as Sunday is 1 in this I would recommend using mod() to make this work.

i.e. assuming the table

create table a ( b date );

insert into a
 select sysdate - level
  from dual
connect by level <= 7;

This would work:

select mod(to_char(b, 'D') + 5, 7) as dd, to_char(b, 'DAY')
  from a
 order by mod(to_char(b, 'D') + 5, 7)

Here's a SQL Fiddle to demonstrate.

In your case your query would become:

select ename, to_char(hiredate,'fmDay') as "Day" 
  from my_table
 order by mod(to_char(hiredate, 'D') + 5, 7)