Re: How to delete duplicate rows with sql

cat@tecsys.com (Catalin Badea) wrote: > > -S.POTHIREDDY (siva@hogpe.ho.att.com) wrote: > : Could any body tell me how to delete duplicate rows in a table. I was > : able to list the duplicate rows in table like this: > : Siva Pothireddy > > Here's one solution: > > select min(rowid) tmp_rowid from _tablename_ group by _key_fields_ > into temp tmp_table with no log; > > delete from _tablename_ > where rowid not in > (select tmp_rowid from tmp_table);> > Replace _tablename_ and _key_fields_ with appropriate names. > > Catalin. > Many solutions have appeared using 'rowid' (I like this one the best). However, what happens when Informix stops using 'rowid' as in version 7.1 with the table fragmentation feature? How can one then distinguish rows from one another?