Anshu Jain's SQL puzzle

Question: If you had one table with one column and some, not all, of the entries were duplicates, how would you find just the duplicates?

 
 

Answer:

CREATE TABLE #myTempTable
(
   myTempCounter int,
   myTempValue nvarchar(50)
)
DECLARE @myNumber int
DECLARE @myWord nvarchar(50)
 
DECLARE curs CURSOR FOR
SELECT Count(myValue) as "counter", myValue
FROM myTable
Group By myValue
 
OPEN curs
FETCH NEXT FROM curs into @myNumber, @myWord
WHILE @@FETCH_STATUS = 0
   BEGIN
      INSERT INTO #myTempTable (myTempCounter, myTempValue)
      VALUES (@myNumber, @myWord)
      FETCH NEXT FROM curs into @myNumber, @myWord
   END
CLOSE curs
DEALLOCATE curs
 
SELECT myTempValue FROM #myTempTable
WHERE myTempCounter > 1
 
DROP TABLE #myTempTable