mysql, select and sorting by group key + with first row that has that key in another column

Question

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.

• Akina: Your current ordering may be obtained using simple ORDER BY unit.
• J. M. Becker: 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.
• Rick James: Show us the desired output.

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.

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