MySQL: save order in UNION

Question

I have a table that stores information about some events.
I would like to list these events, but I would like the upcoming events to be displayed first and in ascending order, and the latter to be displayed below in descending order.

I would like to do something like this:

(SELECT * FROM events WHERE date > NOW() ORDER BY date ASC) UNION (SELECT * FROM events WHERE date < NOW() ORDER BY date DESC) 

I checked the MySQL doc and found out that UNION usually works with an unordered set of rows, so ordering with each of the SELECT is completely useless. None of UNION ALL seem to do the job.

After a short search, I know how to arrange the rows according to which SELECT created them, however I still don't know how to sort them by one column in a different order.

I think I could just do two SELECTs, one for upcoming events, one for the past. But I really wonder if it is possible to do this with just one request. Is there anything you could suggest?

Answer 1

I don’t think you need UNION for this. You should be able to write:

 SELECT * FROM events ORDER BY CASE WHEN date < NOW() THEN date ELSE NOW() END DESC, date ASC ; 

The CASE WHEN date < NOW() THEN date ELSE NOW() END DESC ORDER BY ensures that upcoming events come first and ensure that past events are displayed in the correct order but do not affect the relative order of upcoming events. The date ASC is a fallback criterion that processes this part.

(Disclaimer: not verified.)

Answer 2

To answer a more general question, the UNION operation does not guarantee the preservation of any order of any extracted rows. To get rows returned in a specific order, the only guarantee you have is to use the ORDER BY in the query (external SELECT statement).