mysql :ordering table with day names

Question

Hi.I am trying to get day of names with and correct order like Monday ,Tuesday.. But in my table I have records that after Monday comes Friday or I have Thursday between two Tuesday .I want to order them like Monday ,Monday ,Tuesday ,Tuesday, Wednesday so on .But I don`t want to group them.

I used this query but it does not make order

select Day_Name from mydb.schedule where Room_NO=(510) And Week_NO =(1)

it outputs

Monday
Monday
Tuesday
Wednesday
Wednesday
Tuesday
Thursday
Thursday
Thursday

how can I correct it?

Answer 1

if you actually store the date as a column, you can use the datepart of it... if not, you might need to use a case

case 
   when day_name = "Monday" then 1
   when day_name = "Tuesday" then 2
   when day_name ...
...
..
end as SortBySequence

Answer 2

Change 'day_name' column type to ENUM ('Monday',...,'Sunday'), then sort query by it. ENUM should do the trick.