I'm trying to make a select that outputs everything, but is sorted by a group column with a "header column" that matches said group column on another column.

I have no idea how this type of query should be written. For example, I want the data sorted like this.

id,  unit,  group, col2,  col3,  col4,  col5
99,  ZZX,   null,  test1, test7, test5, test
6,   AAA-B, ZZZ,   test1, test7, test5, test8
77,  AAA-C, ZZX,   test1, test7, test5, test9
101, ZZZ,   null,  test1, test7, test5, test
122, AAB-A, ZZZ,   test1, test7, test5, test11
176, AAB-B, ZZZ,   test1, test7, test5, test12

So basically, the unit should be sorted per each group key, but begins with the row that matches the group key. There are also rows with null for group, but are just header rows. You can even just tell me the high level, and I can write the query, I'm just not sure the best way to approach it.

  • Your current ordering may be obtained using simple ORDER BY unit.
    – Akina
    Feb 25, 2020 at 18:09
  • Yea, my example didn't capture the full data diversity, it isn't at all guaranteed that the group key will be a prefix of the unit. I changed the example output to reflect the data better. Feb 25, 2020 at 18:24
  • Show us the desired output.
    – Rick James
    Mar 2, 2020 at 17:13

1 Answer 1


It was fairly brutal to figure out this answer, but i think a left outer join on the same table matching group to unit column worked. I also had to deal with group column being possibly empty or null.

FROM tablename AS a
LEFT OUTER JOIN tablename AS b
  ON nullif(a.`group`, '') <=> a.unit
ORDER BY COALESCE(b.unit, a.unit);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.