Removing duplicate records

[gregwga50]

I have a file, SFILE that has occasionally has fields SRORD# and SRLINE duplicated



I need to make a copy of this file with the records removed where fields SRORD# and SRLINE are duplicated. Can some suggest the SQL to do it.

Thank you

[B.Hauser]

... are the other column values identical in both records?
If so, try:

CREATE TABLE YourSchema/YourFile
        as (Select Distinct Col1, Col2, .... ColN
              from OldTable)
with Data;

[Vectorspace]

If the other columns are not necessarily identical, try:

create table duplicatesTable as (
  select a.* from myTable a
    left exception join myTable b
    on (a.SRORD#, a.SRLINE) = (a.SRORD#, a.SRLINE)
     and rrn(a) < rrn(b)
) with data;

If there are multiple rows with the same values for SRORD# and SRLINE, it will keep only the last one by relative record number. You could use some other criteria for determining which record to keep

[Vectorspace]

Sorry, there was a typo in my SQL ("... = (a.SRORD#, a.SRLINE)" should have been "... = (b.SRORD#, b.SRLINE)" ), and I cannot edit my replies. Corrected SQL below:

create table duplicatesTable as (
  select a.* from myTable a
    left exception join myTable b
    on (a.SRORD#, a.SRLINE) = (b.SRORD#, b.SRLINE)
     and rrn(a) < rrn(b)
) with data;