There is no need to create temporary tables, very helpful when deleting from large tables, you can delete the duplicate records using inner join and setting the rowcout to 1, the modified version of script.

CREATE TABLE #phonebook (

[phonenumber] [varchar] (30),

[firstname] [varchar] (30),

[lastname] [varchar] (30),

[company] [varchar] (100)

)

INSERT #phonebook SELECT '902','syed','iqbal','sm soft'

INSERT #phonebook SELECT '905','john','ksjl89','company llc'

INSERT #phonebook SELECT '909','joe','average','united'

-- Duplicate insert 1

INSERT #phonebook SELECT '902','syed','iqbal','sm soft'

INSERT #phonebook SELECT '905','john','ksjl89','company llc'

INSERT #phonebook SELECT '909','joe','average','united'

-- Duplicate insert 2

INSERT #phonebook SELECT '902','syed','iqbal','sm soft'

INSERT #phonebook SELECT '905','john','ksjl89','company llc'

INSERT #phonebook SELECT '909','joe','average','united'

--Show Duplicate Phonenumbers in Phonebook

SELECT phonenumber, COUNT(*) FROM #phonebook

GROUP BY phonenumber HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC

SET ROWCOUNT 1

SELECT @@rowcount

WHILE @@rowcount > 0

DELETE pb FROM #phonebook as pb

INNER JOIN

(SELECT phonenumber

FROM #phonebook

GROUP BY phonenumber HAVING count(*) > 1)

AS c ON c.phonenumber = pb.phonenumber

SET ROWCOUNT 0

SELECT * FROM #phonebook

DROP TABLE #phonebook