Posted by apatel85 on 12/28/07 18:13

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

[Back to original message]