My database has a list of events, and I'd like to return it sorted by date. However, I'd like to have the past events be forced below more recent events, so I try to do this:

( SELECT name, date FROM events WHERE date >= curdate() ORDER BY date ) UNION ALL ( SELECT name, date FROM events WHERE date < curdate() ORDER BY date )

This seems to mess up the sort orders, and a little googling has some posts of people saying UNION does not support preserved ORDER for some compatibility reasons.

Is there a workaround to get this functionality?

7 Years
Discussion Span
Last Post by drjohn

ISO SQL says that there can only be one ORDER BY after the last Select, for example:

select * from events where ...
select * from events where ...
order by ...

which orders the complete union-select.

There would be a very simple solution if mysql supports standard OLAP functions like row_number() over ( ... order by...), unfortunately that database is unable to fulfill such standards.


try adding a "fake" field with a fixed valud for each of the selects and use that as your primary sort field:

( SELECT 1 as `temp`, `name`, `date` FROM `events` WHERE `date` >= curdate()  ) UNION ALL ( SELECT 2 as `temp`, `name`, `date` FROM `events` WHERE `date` < curdate()  )
ORDER BY `temp`, `date`

As you are selecting both dates before and dates after, you can do this in a single query by missing out the date condition completely and just sorting by the date!!!

SELECT name, date FROM events ORDER BY date

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.