To sort the query by the top in the day, the rest of the fourth in according to the date of order-CodePudding

Query data table, according to the date of cdate sorting, behind the top that day, and then according to the date of order
For example:

1 the 2020-11-12
2 the 2020-11-12
3 the 2020-12-10;
4 the 2020-11-28
5 the 2020-11-05
6 the 2020-10-10

The day the top can be according to the
Select * from table ORDER BY CASE cdate the WHEN current_date () THEN 0 ELSE 1 END

But this is only the front row in that day, and at the back of the date is random, hope later date will be effected according to the sequence arrangement

thank you

CodePudding user response:

Can put the date minus enough a value, the minimum to date, and then order by

 
Select *, case datediff (cdate, current_date ()) 
When 0 then date_add (cdate, INTERVAL - 99999 DAY) 
The else cdate end as sort_date 
From the table ORDER BY sort_date

CodePudding user response:

The original poster to reverse, you only need to be the reduction of date and the date of upstairs,

CodePudding user response:

Less trouble, directly execute the statement is ok,
SELECT * FROM t. (
SELECT '2020-11-12' D FROM DUAL UNION ALL
SELECT '2020-11-12' D FROM DUAL UNION ALL
SELECT '2020-12-10' D FROM DUAL UNION ALL
SELECT '2020-11-28' D FROM DUAL UNION ALL
SELECT '2020-11-05' D FROM DUAL UNION ALL
SELECT '2020-10-10' D FROM DUAL) T
The ORDER BY the IF (D=DATE_FORMAT (CURRENT_DATE (), 'Y - m - % D % %'), 0, 1), D DESC;