Order By Day_of_week In Mysql

How can I order the mysql result by varchar column that contains day of week name? Note that MONDAY should goes first, not SUNDAY.

Solution 1:

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');

Solution 2:

Why not this?

ORDERBY (
    CASE DAYOFWEEK(dateField)
    WHEN1THEN7ELSE DAYOFWEEK(dateField)
    END
)

I believe this orders Monday to Sunday...

Solution 3:

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.

Solution 4:

This looks messy but still works and seems more generic:

selectday, 
casedaywhen'monday'then1when'tuesday'then2when'wednesday'then3when'thursday'then4when'friday'then5when'saturday'then6when'sunday'then7endas day_nr from test orderby 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 orderby day_nr;

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

Solution 5:

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