Consider the following table: It's a table of 'food' all split into categories, with each sub-category also containing items. A 3-tier hierarchy.
The table is sortable by name, but... what should happen when you sort the Name column?
My current thinking is that it'll sort each category alphabetically within each category. So, first row will be 'Healthy Food' followed by 'Grains' followed by 'Oats' and 'Wheat', with 'Fruit' the next row.
So, the Categories are sorted in order (Healthy Food will be above 'Unhealthy food' in the table) and within each category the groups will be sorted A>Z and finally each item within each group will be sorted alphabetically.
Therefore like this:
However, this isn't strictly 'sorted' because you have 'Orange' above 'Grains' whereas 'G' is obviously earlier in the alphabet that 'O' and we've had feedback in QA testing that it's not behaving as expected. But I think expecting it to be fully A-Z stops it actually being useful.
Is this sorting the best way to handle it? Other than splitting out the 3 tiers into separate columns, I'm not sure if there's a better option than this.