How to eliminate duplicate rows from a table?

jkrao

Hi Frenz! I have a table with 10 Duplicate rows in a SQL Server Database. In that table there is no unique identification column name (eg: SlNo, RowNo... like that) means all rows having a common values and that table does not have any constraints. So I want remove first 9 duplicate rows from that table. How? Kindly give the suggestions to solve this problem. coz I am learner in making Querys in SQL Server 2000. wish me all the best. -- modified at 23:52 Friday 10th February, 2006

Colin Angus Mackay

You could try a SELECT with a GROUP BY clause containg every column name in your table, and the output columns having an additional COUNT(*) For example:

SELECT Column1, Column2, Column3, COUNT(*)
FROM MyTable
GROUP BY Column1, Column2, Column3
ORDER BY DESC COUNT(*)

This will float all the duplicates to the top of the result set. If you have no primary key or unique constraint on any of the columns then you have no way to reliably delete only the duplicates automatically. You will need to take note of the duplicate rows and delete them manually. (Actually, I think there might be, but if you only have 9 duplicates, it would be faster to just do it manually) I'm curious as to why you don't have a primary key on the table? I've never come across a situation where I'd leave out a primary key. Does there look like a good candidate for a primary key? It might be a good idea to create one. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell