Conditional sorting in MySQL

I have a table of conferences that have the following relevant fields:

  1. Conference name (text)
  2. Submission deadline (date)
  3. Notification date (date)
  4. Event start (date)
  5. Event end (date)

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

  1. First, all conferences where the notification is in the future; order these by notification date.
  2. Then, all conferences where the notification is in the past; order these by event start.

I have 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 it does not work, the ordering is incorrect. In fact, I can discern no ordering at all: When I leave out the two ORDER BY clauses, the result is ordered in exactly the same way (i.e., looks random).

Is it possible to achieve this with one single SQL query? Or do I have to make two separate queries?

I am aware of this question: Conditional sorting in MySQL?, but my case is completely different.

Answers


You can do this with a single query by putting all the logic in the 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 explicitly does not guarantee the order of the result set. If you want the result set in a particular order, then you want an order by in the outermost part of the query.


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

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 notification or ev_start as the value to sort on, depending on whether notification is in the future


Need Your Help

Java Netbeans JAR building and running

java build jar executable-jar

I have several packages in my Netbeans project.

jquery load between two urls

jquery

I am trying to load content from 2 different URLS but at different intervals. Ideally I would like to load one url wait 10 seconds load the other and repeat the over and over. This is what I have b...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.