Fetch single value for a record based on a condition on column-CodePudding

I am trying to group records in the ID column and check if any of the status is 1 then substitute the status as 'IN'. If I do not find the value 1 in status then I substitute the status as 'OUT'.

Original Table:

ID	Status
ID_1	1
ID_1	0
ID_2	1
ID_1	0
ID_2	1
ID_3	0
ID_2	0
ID_3	0

The final table should look like this

ID	Status
ID_1	IN
ID_2	IN
ID_3	OUT

Let me know if someone can help :)

CodePudding user response：

SELECT ID, CASE WHEN MAX(Status) = 1 THEN 'IN' 
ELSE 'OUT' END AS Status
FROM myTable
GROUP BY ID

based on coment

SELECT DISTINCT a.ID, COALESCE(b.Status, a.Status)
FROM myTable a LEFT JOIN myTable b ON a.ID = b.ID AND b.Status = 'IN'

CodePudding user response：

Convert 1,0 to IN,OUT and take the min of that (min because 'IN' is less than 'OUT'):

select id, min(case status when 1 then 'IN' else 'OUT' end) as status
from mytable
group by id

If mysql, it's slightly simpler:

select id, min(case status then 'IN' else 'OUT' end) as status
from mytable
group by id

because in mysql, 1 is true and 0 is false.