CODEWITHSUNDEEP
sqloracle-database
Nirpeksh
Nirpeksh

Reputation: 99

How to select a record from duplicate records based on a condition

I have a table X which has three columns SN , OI, FLAG . Some sample values are 

SN OI FLAG
1   a  Y
1   a  N
2   x  N
3   d  N
3   d  Null
4   z  Y
4   z  null
5   k  Y
5   k  Y
5   k  Y
6   l  N
6   l  N

I want the result on the below condition If there are multiple values of same SN , i want the result on the condition that if FLAG has Y and N , then it should show Y , IF Flag has Null and N , it should show N , IF Flag has Y and Null, then is should show Y. SO in the above example this is what I should get . 

SN FLAG
 1 Y
 2 N
 3 N
 4 Y 
 5 Y 
 6 N

Upvotes: 1

Views: 188

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

For your given rules, you can just use MAX():

select sn, max(flag) as flag
from t
group by sn;

Upvotes: 0

forpas
forpas

Reputation: 164064

You can group by sn and get the flag with conditional aggregation:

select sn,
  case
    when sum(case flag when 'Y' then 1 end) > 0 then 'Y'
    when sum(case flag when 'N' then 1 end) > 0 then 'N'
  end flag
from tablename
group by sn
order by sn

In your special case, this should also work:

select sn, max(flag) flag
from tablename
group by sn
order by sn

because 'Y' > 'N'.
See the demo.
Results:

> SN | FLAG
> -: | :---
>  1 | Y   
>  2 | N   
>  3 | N   
>  4 | Y   
>  5 | Y   
>  6 | N

Upvotes: 2

Related Questions