[FIXED] Order by with condition and with field

Issue

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.

Solution

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

Answered By - Akina