Questions [SQL]: SQL deletes duplicate records.

Q: delete repeated records in a field. In table, the ID and phone fields are included. The ID is a non-repeated tag field, but the phone contains duplicate fields. Now, you need to delete the duplicate phone record lines, only the latest record is retained.

A:

If   Object_id ( ' DBO. table1006 ' ) Is   Not   Null
Drop   Table DBO. table1006
Create   Table DBO. table1006
(
[ ID ]   Int ,
[ Phone ]   Varchar ( 10 )
)
Insert   Into DBO. table1006 Values ( 1 , 100 )
Insert   Into DBO. table1006 Values ( 2 , 110 )
Insert   Into DBO. table1006 Values ( 3 , 120 )
Insert   Into DBO. table1006 Values ( 4 , 130 )
Insert   Into DBO. table1006 Values ( 5 , 110 )
Insert   Into DBO. table1006 Values ( 6 , 110 );
Insert   Into DBO. table1006 Values ( 7 , 120 );

Delete   From DBO. table1006
Where   [ ID ]   Not   In ( Select Max ( [ ID ] ) As   [ ID ]  
From DBO. table1006
Group   By   [ Phone ] )
Select   *   From DBO. table1006

Drop TableDBO. table1006