Finding duplicates

[Thorsten Hohage]

Hi Bart,

On 2008-02-07, at 22:34, Mr. Bart Pietercil wrote:

> thanks for chiming in :-)

Oh, I think I filed enough to Mantis tonight :-)


> On 7-feb-08, at 21:19, Thorsten Hohage wrote:
>
>> Hi Bart,
>>
>> On 2008-02-07, at 21:12, Mr. Bart Pietercil wrote:
>>> how do I separate the records that based on firstname and lastname  
>>> are doubles in the table from the rest.
>>> So I want to see the doubles (not the distinct). Probably need to  
>>> use distinct in a subselect but my head refuses cooperation.
>>
>>
>>
>> What do you mean with separate? Do you want to set a flag in that  
>> row, delete them, ...???
>
>
> First I want to compare them, decide which one has to go and which  
> one can stay.
> So let's say that in a list of 1000 users 20 are duplicates. Easiest  
> case 20 times 2 records with identical first and lastnames.
> So I would like to have a query that builds a cursor displaying the  
> 40 records order by first and last name (not grouped) so they are  
> easily comparable to human eyes


When doing it on a customer table (oc_contact) then this query works  
perfectly for me

select idserial, lastname, firstname from oc_contact where  
concat(firstname,'_',lastname) in
(select NAMEIDENTIFIER from
(select DUPCOUNT, NAMEIDENTIFIER from
(select count(*) DUPCOUNT, concat(firstname,'_',lastname)  
NAMEIDENTIFIER from oc_contact where lastname is not null group by  
firstname, lastname ) where dupcount>1))
order by lastname, firstname


Btw. thanks to Ivan who told my successfully how to use a result set  
as a "virtual table" for the next surrounding query.



regards

Thorsten Hohage
--
objectmanufactur.com - Hamburg,Germany