I don't need the exact query , just the logic of how I should go about it:
I need to compare for equality all records of a table for a given set of fields. If count of such similar records exceeds a particular value, I have to delete all those records.

For ex. A table contains columns A, B, C, D, E, F. I want to delete all such records whose column A, C, D are same and if count of such records exceeds (say) 100.

that would come at a later stage.
first you have to figure out the number of records:

``````select A,C,D, count(*) from table
group by A,C,D
``````

count will be the number of records for each unique combination of A,C,D.
If you want to delete all records with …

## All 3 Replies

Just found out that comparison can be done for same table using something like this :
select * from table as t1,table as t2 where t1.column1 = t2.column1

Is this the correct way of doing it?? Can it be further optimized??

that would come at a later stage.
first you have to figure out the number of records:

``````select A,C,D, count(*) from table
group by A,C,D
``````

count will be the number of records for each unique combination of A,C,D.
If you want to delete all records with count > 100 then you would:

``````delete from table inner join
(select A,C,D, count(*) as 'counter' from table
group by A,C,D
having count(*) > 100 ) a
on table.A = a.A
and table.C = a.C
and table.D = a.D
``````

BUT, be warned that this won't delete only the "excess" records (if counter = 120 it won't just delete the 20 records, but the whole 120).
If you want to delete only the 20 you have to:

``````  delete from table inner join
(select A,C,D, ROW_NUMBER() OVER (ORDER BY A ) as 'Row'
from
(select A,C,D, count(*) as 'counter' from table
group by A,C,D
having count(*) > 100 ) a ) b
on table.A = b.A
and table.C = b.C
and table.D = b.D
and Row > 100
``````

Please note that I haven't tested any of these and they may contain typos or other errors.

@adam_k Thanks.. Will try with this and get back 2 u soon...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.