Query to remove duplicate

[Abbas Haider]

Hi

I need to design a query that remove all occurance of duplicates.

Thanks

[si_the_geek]

Thread moved to Database Development forum - which is where SQL questions belong

Your question makes sense, but is not as clear as we need.. for a start, we need to know what database system you are working with (eg: SQL Server 2005/Oracle 10i/....)

As you want an SQL statement, we need to know the names of the table and fields, and preferably the data types of the fields too.

We also need to know what kind of thing you consider to be a duplicate, as there are a couple of common misconceptions about it - it would be useful for you to show us a few example rows of data to help clarify what you want.

[Abbas Haider]

I think this query may be challenging, Be it any DBMS, And talking about fields lets say there is only one field.
Now as you need to know the datatype of the field, consider both cases in which it is string or varchar
Or
The second case is it is Integer or number field.

[si_the_geek]

I didn't ask those questions to be awkward, I asked them because there is information we need to be able to give you a usable answer.. for example, the DBMS involved will have a big effect on what method(s) and syntax are used.

It is also a bad idea to pretend that the tables have different amount/type of fields to what you are actually dealing with - as the structure often has a serious impact too.

[techgnome]

There isn't a blanket generic way of doing this.... for all the reasons si has noted.

However... the logic behind how to do it is fairly generic - it'll be up to you to figure out how to actually implement it.

But basically, you take the table, left join it back to itself, on the fields that are potential duplicates - if there's more than 4, then forget it, and use some other means of scrubbing the data - BUT at the same time, joining on where the PKey does NOT MATCH - you DO have a Primary Key on the table, RIGHT - because if they do match, then you've got the same record joining to itself, which isn't what you want. Then in the where, look for NOT NULLS in the joined PKey....that should give you the duplicates.

Now, do you see why the questions si asked are importaint?

-tg