mysql order per day of the week

Navigation
0

Hello friends, could you help me, I would like to ask a query on a mysql bd to sort the records for the day of the week in which you are currently

I have these records: 

+---------+----------+
| id_cron | day_week |
+---------+----------+
|     126 |        2 |
|     131 |        3 |
|     136 |        4 |
|       1 |        0 |
|       2 |        1 |
|      51 |        5 |
|       3 |        6 |
|      61 |        0 |
|      76 |        0 |
|      36 |        5 |
|      41 |        6 |
|       4 |        0 |
|       5 |        1 |
|       6 |        2 |
|       7 |        4 |
|       8 |        3 |
|       9 |        6 |
|      10 |        0 |
|     141 |        3 |
|      11 |        5 |
|      12 |        6 |
|      13 |        0 |
|      46 |        3 |
|      14 |        4 |
|      15 |        6 |
|      16 |        0 |
|      17 |        5 |
|      18 |        6 |
|      66 |        2 |
|      71 |        3 |
|      19 |        0 |
|      20 |        5 |
|      21 |        6 |
|      96 |        3 |
|      91 |        4 |
|     101 |        2 |
|     106 |        5 |
|     111 |        6 |
|      86 |        2 |
|      81 |        5 |
|     121 |        2 |
|     116 |        4 |
|      31 |        0 |
|      22 |        2 |
|      23 |        3 |
|      24 |        0 |
|      25 |        5 |
|      26 |        6 |
+---------+----------+

Where in the column day_week 2 is Martes , 3 is Miercoles , 4 is Jueves etc

and want to order them by the day number, starting with the day on which we are. Example: Today 2017-09-21 is 4 should be in the order 4,5,6,0,1,2,3 asi: 

+---------+----------+
| id_cron | day_week |
+---------+----------+
|       7 |        4 |
|     136 |        4 |
|     116 |        4 |
|      91 |        4 |
|      14 |        4 |
|      51 |        5 |
|      20 |        5 |
|      11 |        5 |
|      36 |        5 |
|      25 |        5 |
|      81 |        5 |
|     106 |        5 |
|      17 |        5 |
|      26 |        6 |
|      41 |        6 |
|     111 |        6 |
|      21 |        6 |
|      18 |        6 |
|      15 |        6 |
|       3 |        6 |
|      12 |        6 |
|       9 |        6 |
|      16 |        0 |
|       4 |        0 |
|      76 |        0 |
|      61 |        0 |
|      19 |        0 |
|      31 |        0 |
|      13 |        0 |
|       1 |        0 |
|      24 |        0 |
|      10 |        0 |
|       5 |        1 |
|       2 |        1 |
|     126 |        2 |
|       6 |        2 |
|     121 |        2 |
|      66 |        2 |
|      22 |        2 |
|     101 |        2 |
|      86 |        2 |
|     131 |        3 |
|      96 |        3 |
|      71 |        3 |
|      46 |        3 |
|       8 |        3 |
|      23 |        3 |
|     141 |        3 |
+---------+----------+

and tomorrow if the query is executed 2017-09-22 is 5 should be in the order 5,6,0,1,2,3,4

I was trying with the function WEEKDAY(NOW())+1 but I do not know how to apply it correctly

Thank you very much,

greetings

    
asked by skycomputer2 21.09.2017 в 21:11
source

2 answers

1

It is a solution similar to the @WillamsMorales, only that usually weekdays start to count on Sunday, therefore, you must add 1 to the condition of WHEN : 

SELECT id_cron,day_week FROM cron_20170921
ORDER BY 
CASE 
   WHEN day_week - DAYOFWEEK(CURDATE())+1 < 0 
       THEN day_week + DAYOFWEEK(CURDATE())
   ELSE
      day_week - DAYOFWEEK(CURDATE())
   END;

Test code

VIEW DEMO 

CREATE TABLE IF NOT EXISTS cron_20170921 
(
    id_cron INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    day_week INT
)ENGINE=INNODB;




INSERT INTO cron_20170921 (id_cron,day_week)
    VALUES 
    (1,1),
    (2,1),
    (3,1),
    (4,2),
    (5,3),
    (6,4),
    (7,5),
    (8,5),
    (9,5),
    (10,6),
    (11,7),
    (12,7),
    (13,1),
    (14,2),
    (15,3),
    (16,4),
    (17,5)
;



SELECT id_cron,day_week FROM cron_20170921
ORDER BY 
CASE 
   WHEN day_week - DAYOFWEEK(CURDATE())+1 < 0 
       THEN day_week + DAYOFWEEK(CURDATE())
   ELSE
      day_week - DAYOFWEEK(CURDATE())
   END;

Result 

id_cron     day_week
6            4
16           4
7            5
8            5
9            5
17           5
10           6
11           7
12           7
1            1
2            1
3            1
13           1
4            2
14           2
5            3
15           3
    
answered by 21.09.2017 / 23:39
source
1

Try subtracting / adding the index of the day of the week to the value of the column 'day_week', for example: 

SELECT * FROM TableName 
ORDER BY 
CASE 
   WHEN day_week - DAYOFWEEK(CURDATE()) < 0 THEN 
      day_week + DAYOFWEEK(CURDATE())
   ELSE
      day_week - DAYOFWEEK(CURDATE())
END;
    
answered by 21.09.2017 в 21:41
How can I invest a Layout? [closed] Error of variables declared in a stored procedure