need to pull a specific record-CodePudding

There is 1 record having duplicate values except in 1 column having x and y

record status XXXXXXXXXX A XXXXXXXXXX B

Need to pull A only and remove the other duplicate B

Select record case when status in ("'a', 'b'") then ('a')
from xyz

CodePudding user response：

Let suppose you have data as below where Status is repeating for First column

but you are interesting in the status which is of having lower value as given below:

In this case following SQL may help. Here, we are partitioning on key field and ordering the Status so that we can apply filter on rank to get desired result.

WITH sampleData AS
 (SELECT '1234' as Field1,  'A' as STATUS UNION ALL 
  SELECT '1234',  'C' UNION ALL
  SELECT '5678', 'A' UNION ALL 
  SELECT '5678',  'B' )

 select * except(rank) from (
 select *, rank() over (partition by Field1 order by STATUS ASC) rank from sampleData)
 where rank = 1
 order by Field1

CodePudding user response：

Consider below approach

select * from sampledata
qualify 1 = row_number() over win
window win as (partition by field1 order by if(status='A',1,2) )

if applied to sample data in your question - output is