Theodoros80 Theodoros80 - 7 months ago 35
SQL Question

Order by date a select query on UNION ALL

I have little problem with my MySql query:
I have two tables:

Table timeline

id | date | text
1 2013-10-13 Hello


Table reps

id | date | text
1 2013-10-12 Its me again
1 2013-10-11 What?
1 2013-10-10 Lorem ipsum


What i am doing is
UNION ALL
timeline and reps.First row should always be the row from timeline(it's always one row) and then all rows from reps table but in
DESC
order.

My query is the following one(which work ok except from order by)

select id,date,text from timeline UNION ALL select * from reps order by date desc


Think something like a comment (sits on top) with replies on the comment in desc order,newest first.

Thank you in advance.

Answer

Put the UNION in a subquery:

SELECT id, date, text
FROM (SELECT id, date, text, 1 AS priority
      FROM timeline
      UNION ALL
      SELECT *, 2 AS priority
      FROM reps) u
ORDER BY priority, date DESC