0

Screenshot

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>DATE(NOW()) 
ORDER BY date ASC) 

UNION 

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

Please share the query if you know how to do it. Thank you.

  • 1
    ORDER BY in particular subqueries within UNION makes no sense - ordering will die while sorting unified dataset for to delete duplicates. – Akina Feb 14 '19 at 13:27
1

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`>=DATE(NOW()) DESC,
         CASE WHEN fav=0 AND `date`>=DATE(NOW()) 
                  THEN UNIX_TIMESTAMP(`date`)
                  ELSE -UNIX_TIMESTAMP(`date`)
              END ASC;

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .