Order UNION using fields from 2 tables

I开发者_JS百科 am trying to select upcoming events and latest news from tables NEWS and EVENTS and order by NEWS.timestamp and EVENT.datetime

The basic non-ordered query is as follows:

SELECT event_id, title FROM events
UNION
SELECT news_id, subject FROM news

Add the column from each to your select lists with the same column name alias for each. Then just ORDER BY at the bottom.

SELECT
  event_id,
  title,
  datetime AS thedate
FROM events
UNION
SELECT
  news_id,
  subject,
  timestamp AS thedate
FROM news
ORDER BY thedate

A minor variation on Michael's answer (if you want to order by the timestamp and datetime but not show them):

  SELECT
    event_id,
    title
  FROM 
    ( SELECT
        event_id,
        title,
        datetime AS thedate
      FROM events
    UNION ALL
      SELECT
        news_id,
        subject,
        timestamp AS thedate
      FROM news
    ) AS tmp
  ORDER BY thedate