Sort by day of the week, starting with today

A very simple way is this:

SELECT * FROM tabela ORDER BY (
   FIELD(day, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
   - DAYOFWEEK( CURRENT_DATE )
   + 7
) % 7

so the query is always relative to today, and the sequence of strings does not have to be changed.

See working on SQL Fiddle .

Points of interest:

• FIELD returns the position of the string found in the list, from 1 to 7;

• DAYOFWEEK( CURRENT_DATE ) returns the day of the week in the range of 1 to 7;

• we add 7 so that the calculation never gives negative numbers; in many cases, it would not even be necessary, but when it comes to using module, it depends on the specific implementation;

• We use the remainder operator (usually called a module) % to rotate the interval according to the current day.

Tip:

If at some point you convert the table to date instead of char , it's even simpler:

SELECT * FROM tabela ORDER BY (
   DAYOFWEEK( day ) - DAYOFWEEK( CURRENT_DATE ) + 7
) % 7

Maybe not the best option, but one way is to create a column of ordem to know the priority based on the day:

SELECT  events.*,
        CASE 
         WHEN dias.valor > DAYOFWEEK(NOW())
           THEN dias.valor + 1 - DAYOFWEEK(NOW())
         WHEN dias.valor < DAYOFWEEK(NOW())
           THEN dias.valor + 8 - DAYOFWEEK(NOW())
         ELSE 1 END as Ordem
FROM events
INNER JOIN
(
  SELECT 'Sunday' as dia, 1 as valor
  UNION SELECT 'Monday' as dia, 2 as valor
  UNION SELECT 'Tuesday' as dia, 3 as valor
  UNION SELECT 'Wednesday' as dia, 4 as valor
  UNION SELECT 'Thursday' as dia, 5 as valor
  UNION SELECT 'Friday' as dia, 6 as valor
  UNION SELECT 'Saturday' as dia, 7 as valor
) dias on dias.dia = events.day
ORDER BY ORDEM

The INNER JOIN with the day / value is to make my work easier at the time of CASE WHEN , I can know what day of the week is via DAYOFWEEK(NOW()) and based on that I do the operations, eg: p>

• Today is Thursday, the corresponding value is 5 .

• Then my Thursday will become 1 , Friday 2 , Saturday 3 ...

• I check in CASE WHEN if it is the day before today, so I do the VALOR DO DIA + 8 - VALOR DE HOJE operation. So if it's Sunday 1 and today Thursday 5 , 1 + 8 - 5 = 4 .

• I check in CASE WHEN if it is later than today, so I do the VALOR DO DIA + 1 - VALOR DE HOJE operation. So if it is Saturday 7 and today Thursday 5 , 7 + 1 - 5 = 3 .

• Starting with today = 1 .

SqlFiddle DEMO