Order by with condition and with field-CodePudding

I want to put the row that has scheduled date today with status 2 and if its not scheduled date today I want the result of status 1, 2, 3 base on created at. How can I put this on query?

Here's example of the table

table1
id | scheduled_date        | status | created_at
1  | null                  | 1      | 2021-12-20 00.00.00
2  | 2021-12-27 00.00.00   | 2      | 2021-12-19 00.00.00
3  | 2021-12-26 00.00.00   | 3      | 2021-12-23 00.00.00
4  | null                  | 1      | 2021-12-15 00.00.00

Here's the query that I tried so far

select *
from `table1`
where `status` <> 0
order by CASE WHEN date(scheduled_date) = '2021-12-27' and status = 2 then 1 END asc,
         FIELD(status, 1, 2, 3) asc,
         `created_at` asc

But I got the result of row 4,1,2,3 I want the result of row 2,4,1,3

Any help will be appreciated. I'm also trying to do this on laravel so if its eloquent query is much appreciated

CodePudding user response：

I want to put the row that has scheduled date today with status 2 and if its not scheduled date today I want the result of status 1, 2, 3 base on created at.

Test this

ORDER BY CASE WHEN DATE(scheduled_date) = '2021-12-27' AND status = 2 
              THEN 0
              ELSE status  -- or FIELD(status, 1, 2, 3)
              END,
         created_at

CodePudding user response：

I think the nulls are causing the trouble here. Can you try the following

select *
  from `table1`
where `status` <> 0
order by CASE WHEN date(scheduled_date) = '2021-12-27' and status = 2 then 1 
              ELSE 2 
          END asc,
         FIELD(status, 1, 2, 3) asc,
         `created_at` asc

CodePudding user response：

First, use the boolean expression:

DATE(scheduled_date) = '2021-12-27' AND status = 2

in the ORDER BY clause and then simply status and created_at:

ORDER BY DATE(scheduled_date) = '2021-12-27' AND status = 2 DESC,
         status,
         created_at

See the demo.