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

Issue

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:

enter image description here

And I want to end up with something like this:

enter image description here

Solution

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.

Answered By – forpas

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published