Leeloo Leeloo - 7 months ago 70
MySQL Question

MYSQL query for "row-subrows" ordering selection

I have a table, where

SUBROW
shows the ID of a row (if not 0), and indicates whether the row is a subrow of another row.

ID | 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?

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