Theodoros80 Theodoros80 - 2 years ago 114
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
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

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 Source

Put the UNION in a subquery:

SELECT id, date, text
FROM (SELECT id, date, text, 1 AS priority
      FROM timeline
      SELECT *, 2 AS priority
      FROM reps) u
ORDER BY priority, date DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download