CODEWITHSUNDEEP
mysql
Karryanna
Karryanna

Reputation: 117

MySQL: Preserve select order within UNION

I have a table which stores info about some events.
I would like to list those events but I would like the upcoming events to be displayed first and in ascending order while the past ones to be displayed below in descending order.

I would probably 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 MySQL doc and found out that UNION generally works with unordered set of rows so ordering withing each of the SELECTs is quite useless. Neither UNION ALL seems to do the job.

After googling a little bit, I know how to order the rows according to which SELECT produced them, however, I still don't know how to order them by same column in different order.

I guess I could very well just execute two SELECTs, one for upcoming events, one for past ones. But I really wonder whether there's a possibility to do it with just one query.
Is there anything you could suggest?

Upvotes: 4

Views: 731

Answers (2)

spencer7593
spencer7593

Reputation: 108390

To answer the more general question, the UNION operation is not guaranteed to preserve any order of any retrieved rows. To get rows returned in a specific order, the only guarantee you have is to use an ORDER BY clause on the query (the outermost SELECT statement).

Upvotes: 0

ruakh
ruakh

Reputation: 183280

I don't think you need a 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 part of the ORDER BY ensures that upcoming events come first, and ensures that past events show up in the desired order, but doesn't affect the relative order of upcoming events. The date ASC part is a fallback criterion that handles that part.

(Disclaimer: not tested.)

Upvotes: 3

Related Questions