[sk_kireeti]

Hi All,

I have a table "MyTable" with a primary column called "SNO". Let the other columns called A,B,C and D. There is some data in the table.

I want to know if there are any duplicate records. But, as SNO field is generated & inserted by a trigger, there is no chance that it will have a duplicate value. So, we have to compare other columns to get the duplicate records.

I want to know the query which I can use to know the records those were duplicates of others.

Thanks,
Krishna

[jsangeetha]

Hi Krishna


SELECT A,B,C,D, count(*)
FROM MyTable
GROUP BY A,B,C,D
HAVING count(*) > 1


This query should help you identify the duplicate rows. This would group all the records with the values of columns A,B,C and D and then count the no. of rows in that group. If the Count is more than1 then its a duplicate.


Hope this helps.


Sangeetha



Edited by: jsangeetha

[sk_kireeti]

Hi Sangeetha,

Thanks for the reply!!!

Going forward, I have copied the same table onto another DB. But, it is on different character set. So, some characters were interpreted as special characters while copying data. Now I want to know which are the rows those have the changed charaters. I mean the rows that don't match with the old one.

query used to copy table onto new DB is
create table mytable as (select * from mytable@old_db);

NOTE: "old_db" is the DB connection I have in new DB to connect to my old DB.

Thanks,
Krishna