UNION two queries and preserve sort?

[axfv]

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?

[1stDAN]

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

select * from events where ...
union
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.

[hielo]

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`

[drjohn]

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!!!

IE
SELECT name, date FROM events ORDER BY date