Finding Duplicate Records from Tablegreenspun.com : LUSENET : SQL Server Database Administration : One Thread
Is there any way to find Duplicate records from the table in SQL Server.
-- Anonymous, February 15, 2001
In addition to the method that Gary Fryar showed, you can use a self join, and you can use a correlated subquery. For my examples, I will use the pubs database and assume that you want to determine the title and price of the books that have a price that is the same as another book (even if that price is NULL).
The self join method looks like:
select distinct a.title, a.price from titles a, titles b where isnull (a.price, 0) = isnull (b.price, 0) and a.title_id <> b.title_id
The correlated subquery method looks like:
select title, price from titles a where (select count (*) from titles where isnull (price, 0) = isnull (a.price, 0)) > 1
By the way, It looks like Gary provided a PL/SQL (Oracle) solution. The Microsoft SQL Server Transact SQL solution using the having clause (again in the titles table of the pubs database) would look like:
select price, count (price) from titles group by price having count (isnull (price, 0)) > 1
(This method does not allow you to list the title though.)
Hope this helps,
-- Anonymous, February 17, 2001