On my query right now I’m receiving and ID that can be repeated and the status, so I want to group by ID but if any of the rows has status ACTIVE then it should appear ACTIVE.
I have something like this:
And I want to end up with something like this:
All you need is aggregate function
MIN() which will return
ACTIVE when both values are present for an
id, because is considered less than
SELECT id, MIN(status) status FROM tablename GROUP BY id;
A more general solution, that does not depend on the comparison of these particular strings would be with
SELECT id, SUBSTRING_INDEX( GROUP_CONCAT(status ORDER BY status = 'ACTIVE' DESC), ',', 1 ) status FROM tablename GROUP BY id;
See the demo.
Answered By – forpas