0

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 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. – J. M. Becker Feb 25 at 18:24
  • Show us the desired output. – Rick James Mar 2 at 17:13
0

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);
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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