Sort by day of the week starting by today

Question

I want to sort the results by days of the week

I have 7 events, 1 event for each day, and have to order in the following way:

Sunday, Monday, Tuesday, fourth, Thursday, Friday, Saturday

Assuming today is Wednesday he will have to show all the data in this order:

Fourth, Thursday, Friday, Saturday, Sunday, Monday, Tuesday.

Query:

$sqlCmd2 = "SELECT * FROM account.events ORDER BY FIELD(day, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') ASC LIMIT 3";

Is functional.

Answer 1

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 string sequence does not need 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 added 7 so that the calculation never gives negative number; in many cases, nor would be necessary, but when it comes to using module, it depends on the specific implementation;

• we use the split rest operator (usually called module) % to rotate the interval according to the current day.

Tip:

If at any time you convert the table to date instead of char, becomes even simpler:

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

Answer 2

Maybe not the best option, but one way out 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 facilitate my work at the time of the CASE WHEN, I can tell which day of the week is through the DAYOFWEEK(NOW()) and based on that do the operations, example:

• Today is Thursday, the corresponding value is number 5.

• Then my Thursday will turn 1, friday 2, saturday 3...

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

• I check on CASE WHEN if it’s day after today, then I do the operation VALOR DO DIA + 1 - VALOR DE HOJE. So if it’s Saturday 7 and today Thursday 5, 7 + 1 - 5 = 3.

• Starting today = 1.

Sqlfiddle DEMO