CODEWITHSUNDEEP
mysqlsortinggroup-by
Daniel Valland
Daniel Valland

Reputation: 1107

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.

Upvotes: 1

Views: 61

Answers (2)

AgRizzo
AgRizzo

Reputation: 5271

Try 

SELECT COALESCE(replyTo,id) AS replyTo, text
FROM Comments
ORDER BY COALESCE(replyTo,id) DESC, id

Upvotes: 2

Dr. Stitch
Dr. Stitch

Reputation: 918

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;

Upvotes: 0

Related Questions