Q: How can I easily retrieve duplicate records from a physical file using SQL/400? I understand how RowId in Oracle fetches duplicate records, but when I tried using SQL/400 on an iSeries, the Select statements below didn't work.

Select *
  From Table1 A
  Where RowId < (Select Max(RowId)
                   From Table1 B
                   Where A.PrimKey = B.PrimKey)

Error: Column ROWID not in specified tables

Select *
  From Table1 A
  Where *RRN < (Select Max(*RRN)
                  From Table1 B
                  Where A.PrimKey = B.PrimKey)

Error: Token * was not valid

Are there pseudocolumns in SQL/400 similar to RowId in Oracle?

A: SQL/400 doesn't have a RowId pseudocolumn. For a nondistributed table, you can use the RRN function below:

Select  *
  From  Table1 As T1 
  Where RRN( T1 ) < ( Select  Max( RRN( T2 ) )
                        From  Table1 As T2
                        Where T1.ColX = T2.ColX )

This query returns all but one of the rows for each set of rows with duplicate values for the ColX column. This solution may take a while for a large table because of the way the RRN function is implemented. Of course, if there's another column you know won't have identical values for any set of duplicates (on the main column of interest), you can use a statement such as this one:

Select  *
  From  Table1 As T1 
  Where ColY < ( Select  Max( ColY )
                   From  Table1 As T2
                   Where T1.ColX = T2.ColX )

The above tip was originally published in iSeries NEWS by Paul Conte.