Extracting Duplicates from SQL Server 2000

[apatel85]

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

[Roy Harvey (SQL Server MVP)]

On Fri, 28 Dec 2007 10:13:43 -0800 (PST), ap******@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

[Hugo Kornelis]

On Fri, 28 Dec 2007 10:13:43 -0800 (PST), ap******@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

[Erland Sommarskog]

(ap******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Knowledgy]

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
<ap******@gmail.comwrote in message
news:96**********************************@e6g2000p rf.googlegroups.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