V4MD Looking for ideas and solutions for searching double entries

[Ruslan Zasukhin]

Hi Florian,

This is from Ivan:

Hello Ruslan,

      Suppose we have something like this
      
create table test1 (
recID integer,
name varchar(10),
forename varchar(10)
)


Filling our table

insert into test1 values (1, 'Myers',  'Mike' )
insert into test1 values (2, 'Stone',  'Tom' )
insert into test1 values (3, 'Shields',  'Brooke' )
insert into test1 values (4, 'Stone',  'Tom' )
insert into test1 values (5, 'Myers',  'Mike' )
insert into test1 values (6, 'Willis',  'Bruce' )


       Now we need to find out dublicates

       SOLUTION
       --------
       

select recID, name, forename,
( select count(*) from test1 t1 where
                        t1.name = t.name
                and     t1.forename = t.forename ) as c
 from test1 t
order by c DESC, name, forename


Result is
1       Myers   Mike    2
5       Myers   Mike    2
2       Stone   Tom     2
4       Stone   Tom     2
3       Shields Brooke  1
6       Willis  Bruce   1


Now you have a number of opportunities
You can analyze records in the cursor till c != 1 for example

-----------------
Stricly speaking it's not exactly what you want but it works whenever
you do. (At least I think so)

Oracle approach is more interesting (as usual :-) )

Oracle has unique rowid for each row.

So

select  recID, name, forename,
( select count(*) from test1 t1 where
                        t1.name = t.name
                and     t1.forename = t.forename ) as c
 from test1 t
 
 where
    ( select count(*) from test1 t1 where
                        t1.name = t.name
                and     t1.forename = t.forename )  > 1
 
order by name, forename, rowID
                         ^^^^^


Result is
1       Myers   Mike    2
5       Myers   Mike    2
2       Stone   Tom     2
4       Stone   Tom     2