mysql group and sort a UNION

In my guestbook I have 2 tables: messages and replies. Now, I want to get all messages grouped by id (means that message and corresponding replies will be grouped/together) and sorted by date DESC (the newest messages will be first; if a message is the oldest one, but the corresponding reply is the newest of all messages, this group will be on the top of the table), while replies will be sorted by date ASC (oldest reply on the top). Here my mysql query that works good except it doesnt sort replies by date ASC

SELECT msg.id as id, msg.comment, msg.date_added as date_added, 0 as is_reply
  FROM messages AS msg 
UNION 
SELECT reply.msg_id as id, reply.comment, reply.date_added as date_added, 1 as is_reply
  FROM pg_reply as reply 

GROUP BY id 
ORDER BY date_added DESC, is_reply ASC 

is_reply ASC doesnt do the job as I supposed

reply.msg_id specifies id of reply's parent (messages.id)

What the result should look like>

- message A
- oldest reply B 
- ol开发者_如何学JAVAd reply C
- new reply Z  // this is the newest message in the guestbook 
- newer message E // is newer than A but older than the newest message in the guestbook, which is Z
- reply F // (this reply is newer than all messages but message Z)

---

For this answer I'm going to assume that reply.msg_id is a linkfield to the original message.

SELECT id, comment, date_added, is_reply FROM (
  SELECT 
    msg.id as id
    , msg.comment
    , msg.date_added as date_added
    , 0 as is_reply
    FROM messages AS msg 
UNION 
  SELECT 
    reply.msg_id as id
    , reply.comment
    , reply.date_added as date_added
    , 1 as is_reply
  FROM pg_reply as reply ) AS allmsg
ORDER BY id DESC, is_reply, date_added DESC

This works assuming that msg_id is an autoincrement field and that newer id's also have a newer date_added timestamp.

Remarks on the original code
In your original code you have

GROUP BY id 
ORDER BY date_added DESC, is_reply ASC

The GROUP BY implicitly orders on id ASC; the following ORDER BY overrides that and orders by date_added first and is_reply second.
However if date_added is a datetime then the chance of two post having the same time are remote (esp. for replies, it takes time to write them),
so the 2nd order clause hardly ever gets used.

GROUP BY should only be used if you have an aggregate function in your select, such as SUM or COUNT

If you want to remove duplicates from your select do not use group by, use distinct as in select distinct a,b,c from table1 where ...

---

similar solution:

SELECT sort, project, reviewdate, reviewby, subject, venue, filename, remarks1, remarks2, url
FROM (

   SELECT '1' AS sort, project, last_updated AS reviewdate, reviewby, concat( project, '(', 
    TYPE , '): ', subject ) AS subject, venue, filename, remarks1, remarks2, concat( project, '/', 
    TYPE , '/', filename ) AS url
   FROM `upload_cmg` 
   WHERE (
     (
      subject LIKE '%prt%'
      OR project LIKE '%prt%'
      OR TYPE LIKE '%prt%'
     )
     AND (
      subject LIKE '%mouda%'
      OR project LIKE '%mouda%'
      OR TYPE LIKE '%mouda%'
     )
   )
   UNION SELECT '2' AS sort, project, last_updated AS reviewdate, reviewby, concat( project, '(', 
    TYPE , '): ', subject ) AS subject, venue, filename, remarks1, remarks2, concat( project, '/', 
    TYPE , '/', filename ) AS url
   FROM `upload_cmg` 
   WHERE subject LIKE '%mouda%'
   GROUP BY url
) AS vin
ORDER BY sort, reviewdate DESC 

---

I would recommend adding a message parent field in both and sorting on that as your primary sort, with date then being the sort after that.. otherwise you will have replies showing up mixed up with other messages that were posted between replies. You can have the message parent of a non-reply message be itself.

---

Try this:

SELECT id, comment, date_added, is_reply
FROM (
    SELECT msg.id as id, msg.comment, msg.date_added as date_added, 0 as is_reply
      FROM messages AS msg 
    INNER JOIN pg_reply as reply
    ON reply.msg_id = msg.id
    GROUP BY msg.id, msg.comment, msg.date_added, 0 as is_reply
    ORDER BY CASE WHEN MAX(reply.date_added) > msg.date_added THEN MAX(reply.date_added) ELSE msg.date_added END DESC
    UNION 
    SELECT reply.msg_id as id, reply.comment, reply.date_added as date_added, 1 as is_reply
    FROM pg_reply as reply 
    ORDER BY date_added ASC ) a
ORDER BY id