Extracting Duplicates from SQL Server 2000

Hey Guys,

Total Number of Records (Based on 5 fields): 1000
Total Unique Records (Based on 5 Fields): 990
Total number of fields: 5

I have question regarding extracting duplicates from the dataset. I
have 2 fields that makes a record unique. I have used group by
function to find duplicates and got 10 records that are duplicating.
Each records duplicating 1 times, thus, 10 unique records and 10
duplicates, giving me 990 unique records out of 1000.

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?

Thank you very much for your help.

--AP

Re: Extracting Duplicates from SQL Server 2000

On Fri, 28 Dec 2007 10:13:43 -0800 (PST), apatel85@gmail.com wrote:

>Now, I want to filter out duplicates from the dataset and get unique
>records, including all 5 fields.

I'm not at all clear what you are trying for, but perhaps adding
DISTINCT to your query will do it.

Roy Harvey
Beacon Falls, CT

Re: Extracting Duplicates from SQL Server 2000

On Fri, 28 Dec 2007 10:13:43 -0800 (PST), apatel85@gmail.com wrote:

>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.
(snip)

Hi AP,

Your problem is not clear to me. I suggest that you post your table
structure (as a CREATE TABLE statement, including constraints,
properties and indexes), a few well-chosen rows of sample data (as
INSERT statements) to illustrate the problem, and the output required
from that sample data. See www.aspfaq.com/5006 for more details.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: Extracting Duplicates from SQL Server 2000

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Extracting Duplicates from SQL Server 2000

Please post example DDL. It seems like a insert...select distinct query
would get your results if all columns for each duped row is exactly the same

--
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org

Atlanta's Business Intelligence and Data Warehouse Experts


wrote in message
news:960e924b-2856-4c60-82fe-b04707c42990@e6g2000prf.googleg roups.com...
> Hey Guys,
>
> Total Number of Records (Based on 5 fields): 1000
> Total Unique Records (Based on 5 Fields): 990
> Total number of fields: 5
>
> I have question regarding extracting duplicates from the dataset. I
> have 2 fields that makes a record unique. I have used group by
> function to find duplicates and got 10 records that are duplicating.
> Each records duplicating 1 times, thus, 10 unique records and 10
> duplicates, giving me 990 unique records out of 1000.
>
> 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?
>
> Thank you very much for your help.
>
> --AP