Sort in case of mysql

Question

SELECT * FROM case_study ORDER BY CASE WHEN expiry_date_case > CURDATE() THEN 3 WHEN expiry_date_case IS NULL THEN 2 WHEN expiry_date_case < CURDATE() THEN 1 END DESC 

The above query works fine, but I want to sort items by the expiration date in ASC in one case and in DESC in one case. How to do this, there must be something like this

pseudo query

 WHEN expiry_date_case > CURDATE() THEN 3 expiry_date_case ASC WHEN expiry_date_case IS NULL THEN 2 WHEN expiry_date_case < CURDATE() THEN 1 expiry_date_case DESC 

Answer 1

Here is a more general sorting form where you can use several conditions for ordering other than date

 SELECT * FROM case_study ORDER BY CASE WHEN expiry_date_case > CURDATE() THEN 3 WHEN expiry_date_case IS NULL THEN 2 WHEN expiry_date_case < CURDATE() THEN 1 END DESC, case when expiry_date_case > CURDATE() then expiry_date_case end, case when expiry_date_case < CURDATE() then expiry_date_case end desc 

Answer 2

Try the following:

 SELECT * FROM case_study ORDER BY CASE WHEN expiry_date_case > CURDATE() THEN 3 WHEN expiry_date_case IS NULL THEN 2 WHEN expiry_date_case < CURDATE() THEN 1 END DESC, ABS(DATEDIFF(CURDATE(), expiry_date_case)) 

All entries:

• with expiry_date_case past CURDATE() will be first,
• then go to NULL entries and then
• records have expiry_date_case < CURDATE() .

Group [1] entries will be in ascending order (within their own group), while group [3] entries will be in descending order.

Demo here