group by ID and filter by condition - if something then mark them as something else-CodePudding

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:

CodePudding user response：

All you need is aggregate function MIN() which will return ACTIVE when both values are present for an id, because is considered less than CANCELLED:

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 GROUP_CONCAT():

SELECT id, 
       SUBSTRING_INDEX(
         GROUP_CONCAT(status ORDER BY status = 'ACTIVE' DESC),
         ',', 
         1
       ) status
FROM tablename
GROUP BY id;

See the demo.