Why does COALESCE order results in this way?

Question

I have a table with three columns, id, comment, and parent. If parent id is null, the comment is the root comment, and if it is not, this means the comment is a reply to another. I use the following query:

SELECT * 
FROM  `comment` 
ORDER BY COALESCE( parent, id ) DESC 
LIMIT 0 , 30

This query orders the last inserted comment with it's replies, but I don't understand the logic. Why is it ordered this way?

Answer 1

The COALESCE() function returns the first non-null argument that is received. So, if you go through each row and compare the parent/id column, you'll see that it is ordered like this:

7 (because parent is null)
2 (because parent is null)
2 (parent is not null, so it is used)
1 (because parent is null)
1 (parent is not null, so it is used)
1 (parent is not null, so it is used)

Which is in descending order, as you specified.

---

I suspect there may be some confusion here. So let me reiterate. COALESCE(parent, id) will return the first value out of those two that is not null. If parent is not null, it is returned. If it is null, it falls back on id and returns that. If you look at a list of those rows side by side and see the return values, it may be more clear:

| parent | id | return_value |
+--------+----+--------------+
|  null  | 7  |       7      |
|  null  | 2  |       2      |
|  2     | 4  |       2      |
|  null  | 1  |       1      |
|  1     | 3  |       1      |
|  1     | 5  |       1      |
|  1     | 6  |       1      |