IT Professionals

[kirukan]

Thanks, I think it is better to identify duplicates using this than the traditional "having count (0) > 1 " , right ?

To me it depends if you just need a list traditional itself will work better if you are going to do further analysis then partition will help.Below is an example from supplier master to identify two different suppliers given same beneficiary name for payment.

--With Partition
WITH party as(
SELECT ROW_NUMBER() OVER (PARTITION BY party_benfname
ORDER BY ptyh_id DESC ) RN,party_benfname,ptyh_id
FROM party_master)

select * from party where party_benfname in(select party_benfname from party where RN>1)

--Old style
select party_benfname,Ptyh_id from party_master where party_benfname in(
select party_benfname from party_master group by party_benfname having count(party_benfname)>1)