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

user_19735191 0
Viewed 76

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
sql
2 Answers

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

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

DBFiddle can check

user_166238160
Related