Ignore 1 is to 1 of 2 values, but show other values-CodePudding

In below table if for an item , there is 101 showing up, then equivalent amount of 111 for the same item should be ignore and only show the remaining 111 values.

( 1:1 of 101:111 should be ignored in output )

item|qty|batch|type

item1|2|ten|111

item1|2|ten|111

item1|2|ten|111

item1|2|ten|101

item1|4|ten|111

item2|2|five|111

item2|2|five|101

item2|2|five|111

so desired output will be as per below, how to obtain this..

item|qty|batch|type

item1|2|ten|111

item1|2|ten|111

item1|4|ten|111

item2|2|five|111

CodePudding user response：

Here's a solution using row_number and count to check the difference between type.

select  item    
       ,qty 
       ,batch   
       ,type
from   (
        select item 
              ,qty  
              ,batch    
              ,type
              ,row_number() over(partition by item,qty,batch order by item)                             as rn
              ,count(case type when 111 then 1 end) over (partition by item,qty, batch order by item) - 
               count(case type when 101 then 1 end) over (partition by item,qty, batch order by item)   as dif
        from t
        ) t2
where rn <= dif and type = 111

item	qty	batch	type
item1	2	ten	111
item1	2	ten	111
item1	4	ten	111
item2	2	five	111

Fiddle

CodePudding user response：

    select distinct(item),qty, batch, type
from(
SELECT item, qty, batch, type
FROM test
where type = 111)x
;

DBFiddle can check