Aggregating rows in SQL

Question

I want to select 'Y' or 'N' over 'U'- whenever there is a two-row of the same id. However, I want to retain 'U' when there is only one row of an id. Can anyone show an efficient approach to aggregate the following table:

into something like this:

I tried the MAX function but it only retains value in alphabetical order and 'U' happens to be in the middle of 'Y' and 'N' therefore MAX function doesn't work as I intended.

Happy to hear your thoughts.

Answer 1

You can use window functions:

select id, ind
from (
    select t.*, row_number() over(
        partition by id 
        order by case ind
            when 'Y' then 1
            when 'N' then 2
            when 'U' then 3
            else 4           -- is this possible?
        end
    ) rn
    from mytable t
) t
where rn = 1

Alternatively, we can turn the strings to numbers, pick the preferred value, and then translate back to the original string :

select id,
    case min(
        case ind
            when 'Y' then 1
            when 'N' then 2
            when 'U' then 3
        end
    )
        when 1 then 'Y'
        when 2 then 'N'
        when 3 then 'U'
        else '??'
    end as ind
from mytable
group by id