By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

Extracting Duplicates from SQL Server 2000

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
(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

P: n/a
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.