Ever need to pull up all columns for all rows with a duplicate field? Try this query .
Select * from dbo.Table where ID inBasically 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.
(SELECT ID
FROM
(SELECT ID,COUNT(*) AS ItemCount
FROM dbo.Table
GROUP BY ID
HAVING COUNT (*) >1
)AS a
)
ORDER BY ID
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.
No comments:
Post a Comment