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

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

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

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


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

(apatel85 (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

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


<apatel85 (AT) gmail (DOT) com> wrote