Wednesday, October 08, 2014


Ever need to pull up all columns for all rows with a duplicate field?  Try this query .
Select * from dbo.Table where ID in
    (SELECT ID
     FROM
        (SELECT ID,COUNT(*) AS ItemCount
         FROM dbo.Table
         GROUP BY ID
         HAVING COUNT (*) >1
         )AS a
    )
ORDER BY ID
Basically we have three nested queries.  The inner-most pulls up a count grouped by the ID field which have more than one occurrence.  Note the "AS a" tag.  This alias is required on MS SQL for this to work.

The next query (next as the next inner-most) selects only the ID field from our inner query.  This is so we can use this in the last query, which selects all rows that have ids which match the returned set of ID's.