Conditional sorting in MySQL

Question

I have a conference table that have the following relevant fields:

• Conference name (text)
• Deadline for submission (date)
• Date of notice (date)
• Event start (date)
• End date (date)

I would like to request all the conferences where the end of the event is greater than, say, today, and organize them as follows:

• Firstly, all conferences at which notification will be in the future; order them by date of notification.
• Then all the conferences at which the notification was sent in the past; order them at the start of the event.

I tried:

(SELECT * FROM conferences WHERE ev_end >= '2014-06-30' AND notification >= '2014-06-30' ORDER BY notification ASC) UNION (SELECT * FROM conferences WHERE ev_end >= '2014-06-30' AND notification < '2014-06-30' ORDER BY ev_start ASC); 

But this does not work, the order is wrong. In fact, I cannot discern the order at all: when I omit the two ORDER BY clauses, the result is ordered in exactly the same way (i.e., it looks random).

Is it possible to achieve this with a single SQL query? Or do I need to make two separate requests?

I know about this question: Conditional sorting in MySQL? but my case is completely different.

Answer 1

You can do this with a single request by placing all the logic in an order by clause:

 select * from conferences where ev_end >= '2014-06-30' order by (notification >= '2014-06-30') desc, (case when notification >= '2014-06-30' then notification end) asc, (case when notification < '2014-06-30' then ev_start end) asc; 

Note that union does not explicitly guarantee the order of the result set. If you want the result set to be in a specific order, then you want order by in the outermost part of the query.

Answer 2

 SELECT * FROM conferences WHERE ... ORDER BY notification < '2014-06-30', IF(notification < '2014-06-30', ev_start, notification) 

Answer 3

 SELECT * FROM conferences WHERE ev_end >= '2014-06-30' ORDER BY IF(notification >= '2014-06-30', notification, ev_start) ASC 

And IF is used to return either a notification or ev_start as a value for sorting, depending on whether there will be a notification in the future