Finding duplicates

[Mr. Bart Pietercil]

Thanks Thorsten,

with a bit of modification it worked

select  
RecID 
,user_lastname 
,user_firstname 
,user_mailaddress,user_logonname,user_password,objptr_modified_by- 
 >user_lastname As 'Modified by' from tbl_users where  
concat(user_firstname,'_',user_lastname) in
(select NAMEIDENTIFIER from
(select DUPCOUNT, NAMEIDENTIFIER from
(select count(*) AS DUPCOUNT, concat(user_firstname,'_',user_lastname)  
AS NAMEIDENTIFIER from tbl_users where user_lastname is not null group  
by user_firstname, user_lastname ) where dupcount>1))
order by user_lastname, user_firstname


However I could surely do with a bit of explaining as to  why this  
works.

Maybe interesting for Ruslan: query takes 17 seconds on a table with  
1000 records (Can't say whether this is good or bad)


Thx,

Bart


On 8-feb-08, at 00:20, Thorsten Hohage wrote:

> 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
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina