kohnen,

Is there any way to find Duplicate records from the table in SQL Server.

Thnaks, Ravi.

-- Anonymous, February 15, 2001

Answers

Ravi,

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,

Eric

-- Anonymous, February 17, 2001