MYSQL query for "row-subrows" ordering selection

Question

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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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.

MYSQL query for "row-subrows" ordering selection

Sign up