Need help in query logic

[DJSAN10]

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.

[DJSAN10]

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??

[adam_k]

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.

[DJSAN10]

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