Re: Selecting SIMILAR, not the same records (PROBABLE) duplicates

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 05 Sep 2006 16:07:53 -0700
Message-ID: <1157497673.708654@bubbleator.drizzle.com>

kroger wrote:

>> kroger wrote:

>>> Hi,
>>>
>>> I've been struggling with that for two days now...
>>> There is a simple solution for finding duplicates - with GROUP BY and
>>> HAVING COUNT(*)>1 but it is not enough in my case...
>>>
>>> For the example table as follows:
>>>
>>> id || name
>>> 1 || aaa
>>> 2 || aaa xxx
>>> 3 || aaa
>>> 4 || aaah
>>> 5 || bbb
>>> 6 || bbb p
>>> 7 || ccc

> 
>> If you think ID1 = ID2 and ID1=ID4 you are not looking for duplicates.
>> It is no wonder no one's proposed solution worked.
> 
> I know they are not duplicates. That;s why I put SIMILAR and PROBABLE 
> duplicates in the topic...
> Bad thing is, I need to have some way to display say element A (of  whatever 
> id 1-4) and all its PROBABLE duplicates... Tricky, but requested...

And if you have this what do you do?

ID
1 aaa
2 aaa h
3 h
4 h aaa

The request makes no business sense. Here's what I would suggest:

SELECT DISTINCT name
FROM table;

Spool the output, send it to the manager of the department, and ask them to sort it out.

Alternatively ask for a list of legal values. Create a foreign key constraint and blow everything that doesn't match out of the table so they can sort it out.

This is not a programming problem ... this is a business problem and there really is no solution other than a subject matter expert looking over the garbage value by value.

PS: rename your columns.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Tue Sep 05 2006 - 18:07:53 CDT