Please advise on finding duplicates rows based on 3 columns of a table. I am trying to avoid subqueries..

Example: payment table - policy,amount,coverage are the columns
duplicate row is when all policy, amount, coverage have same data repeated.

Maybe this query might work (but it depends on what you want to do with the result):

select policy, amount, coverage, count(*) from payment
group by policy, amount, coverage
having count(*) > 1

Another possibility is the ROWNUMBER() OVER (PARTITION BY policy,amount,coverage ORDER BY ... )

The result from the SELECT is a sequence number column.

HI

GertK gave you some very useful advise there, thanks Gert.

For other DB2 DBA;s out there, this query he sent will also assit you in determining within an unique index, what are the duplicates. Just remeber to put the columns of the index in the select and group bu clauses.