Daniel Valland Daniel Valland - 5 months ago 31
MySQL Question

MYSQL Group rows and sort each group

I have a mysql table for storing comments and subcomments with the schema:
id,replyTo,text where replyTo may be null, if the comment is not a sub-comment.

Currently i am doing the following query:

(select id as replyTo, text from Comments WHERE replyTo IS NULL)
UNION
(select replyTo,text from Comments WHERE replyTo IS NOT NULL) ORDER BY replyTo;


Which gives me the comments in the order:

1. comment
2. subcomment


So if i have:

+----+---------+-----------+
| id | replyTo | text |
+----+---------+-----------+
| 1 | NULL | comment 1 |
| 2 | NULL | comment 2 |
| 3 | 1 | sub 1 |
| 4 | 2 | sub 2 |
+----+---------+-----------+


This gives:

+---------+-----------+
| replyTo | text |
+---------+-----------+
| 1 | comment 1 |
| 1 | sub 1 |
| 2 | comment 2 |
| 2 | sub 2 |
+---------+-----------+


My question is, how do i get them in the reversed order, that is, like this:

+-+------------+
|2|comment 2 |
|2|sub 2 |
|1|comment 1 |
|1|sub 1 |
+-+------------+


In other words, i would like to sort the elements by replyTo ASC, group them by replyTo (not as in mysql GROUP BY, which would remove all duplicate rows based on attribute), and then sort the groups by replyTo DESC. If that makes sense.

Answer

Try this:

select replyTo, text from
(
select id as replyTo, text from Comments WHERE replyTo IS NULL 
UNION 
select  replyTo,text from Comments WHERE replyTo IS NOT NULL ORDER BY replyTo
)
order by replyTo DESC;