Sql Rank for records contain multiple combinations 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 &gt; 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
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 &gt; 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(*) &gt;1)
    )tmp2 ON (tmp2.store = tmp.store)

order by tmp.Store