CODEWITHSUNDEEP
mysql
Sean Cunningham
Sean Cunningham

Reputation: 3106

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

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!

Upvotes: 3

Views: 857

Answers (2)

Sebas
Sebas

Reputation: 21522

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.

Upvotes: 3

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Another version, joining the two tables twice, once to get the sortorder value, once to get the max for the type, then order accordingly;

SELECT d.* 
FROM dashboard_items d
JOIN dashboard_item_controls dc ON d.diid=dc.diid
JOIN (
  SELECT MAX(sortorder) mx, type FROM dashboard_item_controls dic
  JOIN dashboard_items di ON di.diid=dic.diid
  GROUP BY type
) a
ON d.type = a.type 
WHERE dc.status = 'on' ORDER BY a.mx,dc.sortorder

An SQLfiddle to test with.

Upvotes: 0

Related Questions