how to remove redundant records

[michael123]

I have a table in MS SQL DB, the data was recorded with redundancy, is there an easy way to remove all but keep only one, for example:

id   name   age   dept
---------------------------
1    alan      20    A
2    william   23   B
2    william   23   B
3    mike      30   C
3    mike      30   C

I want to trim it to:

id   name   age   dept
---------------------------
1    alan      20    A
2    william   23   B
3    mike      30   C

what is the easy way to do that? DB running on MS SQL 2005.

Any idea?

[Ramy Mahrous]

http://support.microsoft.com/kb/139444

[Doron_]

There are few ways to do that, the simple one i think is by adding an index column, and than delete all records which has identical records with smaller index value:

ALTER TABLE MyTable ADD i int IDENTITY;

DELETE FROM MyTable
WHERE i IN (
   SELECT T1.i 
   FROM MyTable AS T1
   INNER JOIN MyTable AS T2 ON T1.id=T2.id
   WHERE T1.i > T2.i
)

ALTER TABLE MyTable DROP COLUMN i;

Doron Saar,

Nob Hill Software - tools for database people (+ free stuff!) Nob Hill Software - tools for database people (+ free stuff!)
www.nobhillsoft.com