sorting in case 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 work's fine, But i want to sort the items by expiry date in ASC in one case and DESC in one case.How to acheive this it should be some thing 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 generic form of doing the sorting, where you can use multiple 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