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

thanx