469,623 Members | 1,422 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

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
Dec 28 '07 #1
4 2834
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
Dec 28 '07 #2
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
Dec 28 '07 #3
(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
Dec 28 '07 #4
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

Jan 9 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Gnaneshwar Babu | last post: by
2 posts views Thread by mGracz | last post: by
3 posts views Thread by ryan.paquette | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.