Is possible to use ORDER BY with IF ELSE condition? Here I have some data. I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the remaining records in descending order(date). I have written a query, but it is not working as expected:

(SELECT * 
FROM `test` 
WHERE fav = 1 
ORDER BY date DESC) 

UNION 

(SELECT * 
FROM `test` 
WHERE fav = 0 
    AND date&gt;DATE(NOW()) 
ORDER BY date ASC) 

UNION 

(SELECT * 
FROM `test` 
WHERE fav = 0 
    AND date&gt;=DATE(NOW()) 
ORDER BY date DESC)


Please share the query if you know how to do it. Thank you.
I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the remaining records in descending order(date).


Try:

SELECT * 
FROM `test`
ORDER BY fav=1 desc,
         fav=0 AND `date`&gt;=DATE(NOW()) DESC,
         CASE WHEN fav=0 AND `date`&gt;=DATE(NOW()) 
                  THEN UNIX_TIMESTAMP(`date`)
                  ELSE -UNIX_TIMESTAMP(`date`)
              END ASC;