mardi 10 février 2015

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 > 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





Aucun commentaire:

Enregistrer un commentaire