Order by day_of_week in MySQL

Question

How can I order the mysql result by varchar column that contains day of week name?

Note that MONDAY should goes first, not SUNDAY.

Answer 1

I'm thinking that short of redesigning the column to use an enum instead, there's not a lot to be done for it, apart from sorting the results after you've gotten them out.

Edit: A dirty hack is of course to add another table with id:weekday pairs and using joins or select in selects to fake an enum.

Answer 2

Either redesign the column as suggested by Williham Totland, or do some string parsing to get a date representation.

If the column only contains the day of week, then you could do this:

ORDER BY FIELD(<fieldname>, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY');

Answer 3

Another way would be to create another table with those days and an int to order them by, join that table when searching, and order by it. Of course, joining on a varchar is not recommended.

Table DaysOfWeek
id       | day
--------------------
1        | Monday
2        | Tuesday
3        | Wednesday
4        | Thursday
5        | Friday
6        | Saturday

SELECT * FROM WhateverTable LEFT JOIN DaysOFWeek on DaysOFWeek.day = WhateverTable.dayColumn ORDER BY DaysOfWeek.id

(Apologies if that's not correct; I've been stuck with SQL server recently)

Again, this is NOT recommended, but if you cannot alter the data you've already got... This will also work if there are non-standard values in the dayColumn field.

Answer 4

This looks messy but still works and seems more generic:

select day, 
case day
  when 'monday' then 1
  when 'tuesday' then 2
  when 'wednesday' then 3
  when 'thursday' then 4
  when 'friday' then 5
  when 'saturday' then 6
  when 'sunday' then 7
end as day_nr from test order by day_nr;

Using if is even more generic and messier:

select id, day, 
if(day = 'monday',1,
  if(day = 'tuesday',2,
    if(day = 'wednesday',3,
      if(day = 'thursday',4,
        if(day = 'friday',5,
          if(day = 'saturday',6,7)
        )
      )
    )
  )
) as day_nr from test order by day_nr;

You can also hide the details of conversion from name to int in stored procedure.

Answer 5

... ORDER BY date_format(order_date, '%w') = 0, date_format(order_date, '%w') ;

Answer 6

@shantanuo...not sure how that even works, but that is it. Good work. I wish I could vote this to the top.