If I follow you correctly, you want the
id in the
GROUP BY clause:
GROUP BY l.order, dev.id
It is a golden rule in SQL that all non-aggregated columns in the
SELECT clause should be repeated in the
GROUP BY clause (unless they are functionally dependent on another column that belongs to that clause – but that’s another story).
MySQL has been lax about that in early versions, causing somehow counter-intuitive behaviors that are then hard to debug if you don’t have that rule in mind. Make sure to always enable sql mode
ONLY_FULL_GROUP_BY, so such problems are treated as syntax errors.
Note that this modified
GROUP BY clause uses the actual column names rather than their alias; that’s standard SQL as well (while MySQL allows otherwise).
CLICK HERE to find out more related problems solutions.