0

I am trying to filter the records which contains multiple combinations and removing the records which contains a single combination. Database DB2

Query Used:

select * 
from 
    (select b.Store,b.order, 
            rownumber() over (partition by b.store order by b.order) AS RNK 
     from Library.Table1 b
    ) tmp 
where tmp.RNK > 1 
order by tmp.Store

Current output:

Store Order RNK

Store1  Order1  1
Store2  Order1  1
Store2  order2  2
Store2  order3  3
Store3  Order1  1
Store3  order2  2
Store3  order3  3
Store4  Order1  1

Expected output:

Store Order RNK

Store2  Order1  1
Store2  order2  2
Store2  order3  3
Store3  Order1  1
Store3  order2  2
Store3  order3  3
1

I'm not familiar DB2, but if it allows using count as analytic function you can do

select * 
from 
    (select b.Store,b.order, 
            rownumber() over (partition by b.store order by b.order) AS RNK ,
            count(*) over (partition by b.store) cnt
     from Library.Table1 b
    ) tmp 
where tmp.cnt > 1 
order by tmp.Store

If not, the query below should give you the same result (maybe not the best solution from performance perspective ) :

select tmp.* 
from 
    (select b.Store,b.order, 
            rownumber() over (partition by b.store order by b.order) AS RNK 

     from Library.Table1 b
    ) tmp 
    INNER JOIN 
    (
        SELECT store 
        FROM Library.Table1  
        GROUP BY store
        HAVING (COUNT(*) >1)
    )tmp2 ON (tmp2.store = tmp.store)

order by tmp.Store

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.