Posted by Erland Sommarskog on 12/28/07 22:49

(apatel85@gmail.com) writes:
> Now, I want to filter out duplicates from the dataset and get unique
> records, including all 5 fields. Here is the query that I used.
>
> select *
> from (
> select field1, field2, count(*)
> from table 1
> group by field 1, field2
> having count(*) >1
> )a,
> table 1 b
> where a.field1=b.field1 and a.field2=b.field2
>
> When I use this query, it gives me duplicate records as i am matching
> on both fields that makes records unique. Is there anyway to just
> extract unique records?
>

Could this do?

SELECT a.*
FROM tbl a
WHERE EXISTS (SELECT *
FROM (SELECT field1, field2
FROM tbl
GROUP BY field, field2
HAVING COUNT(*) > 1) AS b
WHERE a.field1 = b.field1
AND a.field2 = b.fiedl2)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Database Tripled In Size!!
• Prev in forum: Re: Database Tripled In Size!!
• Thread view: Re: Extracting Duplicates from SQL Server 2000

[Reply to this message]