-2

I'm trying to build a chained list of items, in which the first one has a parent_id = 0, while the other ones should be sorted by what the last element's id was related to its parent_id.

This is the expected result:

| id | parent_id |
|----|-----------|
| 9  | 0         |
| 2  | 9         |
| 3  | 2         |
| 14 | 3         |
| 5  | 14        |

How can I do this kind of ORDER BY clause? Currently I'm at

parent_id = 0 desc, parent_id desc

But the output of this is wrong, of course, because it doesn't depend on the last ID, it's just running a desc ordering of parent_id:

| id | parent_id |
|----|-----------|
| 9  | 0         |
| 5  | 14        |
| 2  | 9         |
| 14 | 3         |
| 3  | 2         |

Thanks in advance.

Community
  • 1
  • 1
Anonymous
  • 850
  • 2
  • 12
  • 26
  • It's the first table of the post - I added a remark on it so it's less confusing. Thanks. – Anonymous May 26 '15 at 11:10
  • No. Now it's more confusing. In order to deduce output, it's useful to see input. – Strawberry May 26 '15 at 11:10
  • My current attempt - parent_id = 0 desc, COALESCE(id, parent_id), id seems to be working for this set of results. I'm trying to match a few edge cases just to be sure. Does it seem fine to you? – Anonymous May 26 '15 at 11:12
  • 2
    You might need a recursive query: http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – rghome May 26 '15 at 11:12
  • You are looking for a MySQL equivalent of Oracle's `CONNECT BY PRIOR`. This answer will help you. http://stackoverflow.com/questions/19132608/is-there-a-connect-by-alternative-in-mysql – CodeNewbie May 26 '15 at 11:32
  • @CodeNewbie pretty cool, I didn't know that. I think my current order by clause solves the issue, but thanks for the link. – Anonymous May 26 '15 at 12:09

1 Answers1

0

Ordering by:

'parent_id = 0 desc, COALESCE(id, parent_id), id'

seems to have fixed my scenario.

Anonymous
  • 850
  • 2
  • 12
  • 26