Order by day_of_week in MySQL

mysql sql sql-order-by dayofweek
Bogdan Gusiev picture Bogdan Gusiev · Jul 14, 2009 · Viewed 21.8k times · Source

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

Glen Solsberry picture Glen Solsberry · Jul 14, 2009

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

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL "Group By" and "Order By"

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` GROUP BY LOWER(`fromEmail`) ORDER BY `…

Read More
SQL order string as number

I have numbers saved as VARCHAR to a MySQL database. I can not make them INT due to some other depending circumstances. It is taking them as character not as number while sorting. In database I have 1 2 3 4 5 6 7 8 9 10... On my page …

Read More