how to select distinct values from a table when i need to select values having y if it has both y,n values and n values only if there is no y value

i have a table with data like

promo    flag 

ab21     y 
ab21     N  
bc22     N  
bc22     N  
CD23     Y  
CD23     Y 
ef24     N
ef24     y

i want distinct promo ,flag from the table but when a single promo has both Y,N flags i want only Y FLAG PROMO

i.e expected output

ab21 y
bc22 n
cd23 y
ef24 y

need help in building a simple query

like

select distinct promo,flag from table where ( your solution subquery)

Answer

You can use analytical function like this:

Select promo, flag 
 from (Select promo, flag,  
       row_number() 
       over (partition by promo order by upper(flag) desc nulls last) as rn 
 From your_table) 
Where rn=1;

You can also use group by but it will restrict you to select any other columns from table in the result.

select promo, max(upper(flag)) from table
group by promo

Also, in group by query, it will be case insensitive so , I would recommend to use analytical function.

Cheers!!