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