Need MySQL to group by one column based on the order of another

Question

Some sample data:

dashboard_items
diid      name          type
1         Tasks         large
2         Work Orders   large
3         Favorites     small
4         Sign-Ons      small
5         Sales Metrics chart

dashboard_item_controls
dicid    status      sortorder      diid
1        on          1              3
2        on          2              2
3        on          3              4
4        on          5              5
5        on          4              1

My query would provide me with the following results:

diid      name          type
3         Favorites     small
4         Sign-Ons      small
2         Work Orders   large
1         Tasks         large
5         Sales Metrics chart

What I'm trying to do here is to have the query identify the item with the highest sortorder and then sort all items of the same type underneath it, using sortorder to do so. Then, the query should find the next type and do the same thing until all items marked with a status of "on" have been sorted. This would allow users to rearrange specific items but only within the group (type) itself. They could also rearrange entire groups (e.g. moving the charts above the small items) but they couldn't, for example, put a small item followed by a large item, followed by another small item.

I'm starting with the following query as a baseline:

SELECT dashboard_items.diid, dashboard_items.name, dashboard_items.type
FROM dashboard_item_controls
JOIN dashboard_items ON dashboard_item_controls.diid = dashboard_items.diid
WHERE status = 'on'
ORDER BY sortorder ASC

But of course that just sorts purely by sortorder and ignores the type field. I've tried variants of GROUP BY and ORDER BY and haven't had any luck. Can anyone point me in the right direction? Thanks in advance!

Answer 1

Thought about this: making groups according to their types first and their on top of it, sorting it in the groups by sort order:

SELECT DISTINCT d.diid, d.name, d.type
FROM (
    SELECT d2.type, MIN(d1.sortorder) as srt
    FROM dashboard_item_controls d1
        JOIN dashboard_items d2 ON d1.diid = d2.diid
    WHERE d1.status = 'on'
    GROUP BY d2.type
    -- ORDER BY srt ASC -- As suggested, this is not required
    ) t JOIN dashboard_items d ON d.type = t.type
        JOIN dashboard_item_controls dd ON dd.diid = d.diid
WHERE dd.status = 'on'
ORDER BY t.srt ASC, dd.sortorder ASC

I didn't take time to create the fiddle, maybe there are some tipos left. Hope it helped.