MySQL Question
MYSQL query for "row-subrows" ordering selection
I have a table, where
SUBROWID | SUBROW
1 | 0
2 | 0
3 | 1
4 | 2
5 | 1
I need a query, which would select in the following order:
ID | SUBROW
1 | 0
3 | 1
5 | 1
2 | 0
4 | 2
Ideas?
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
Assuming the parent's id is always less than the childs, you could use something like: ORDER BY LEAST(ID, SUBROW), ID to order first by the parent's then the child's.
...actually, assuming only two "layers" (parents and children, no grandchildren), the parent id does not need to be less than the childs
ORDER BY IF(SUBROW = 0, ID, SUBROW), SUBROW <> 0, ID
or
ORDER BY CASE WHEN SUBROW = 0 THEN ID ELSE SUBROW END, SUBROW <> 0, ID
This orders by the parent id (if there is one) first, then whether the row had a parent, and then the final , ID is only necessary if you want to guarantee the order of the children cannot not change from one execution of the query to the next.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download