AW: AW: Looking for double entries

[Ruslan Zasukhin]

on 10/2/03 11:53 AM, Florian Bogeschdorfer at fb at memedia.de wrote:

>> In Valentina 1.x you can do next:
>> 
>>  SELECT COUNT(`id`), `nachname`, `vorname`  FROM `menschen`
>> GROUP BY `nachname`, `vorname`  ORDER BY 1, 2, 3
>> 
>> This will give you something as:
>> 
>> 1   Florian    Bogesdorfer
>> 1   Mark       Doe
>> 2   Florian    Bogeschdorfer
>> 2   Ruslan     Zasukhin
>> 
>> So you now can easy see what is wrong
>> 
>> --
> Maybe, but that is not the same. I need a cursor with a list of records,
> where name and vorname are the same in different records. And it should be
> sorted in a way that those records with same name and vorname are
> together...
> 
> I found this SQL query in net web, but it gives me error 616
> 
> SELECT * FROM KUNDENtable A WHERE 1 < (SELECT COUNT(*) FROM KUNDENtable I
> WHERE A.name = I.name)

This will not work.

> What can I do?

Hmm, what about next:

SELECT nachname, vorname
FROM menschen
WHERE (nachname, vorname)  in
  (  
    SELECT `nachname`, `vorname`, count(*) as C
    FROM `menschen`
    GROUP BY `nachname`, `vorname`
    Having C > 2
  )  


Ops, 
    * Valentina cannot do HAVING yet ...
    * I DO NOT know any DBMS which support
            (nachname, vorname) syntax
      we will support this in Valentina 2.0


Florian, I have CC this to Valentina list,
But I think please once again send your task description to list.
May be somebody will give you a tip.

Frankly saying your task is reverse to usual tasks...
May be you need additional fields, tables...


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------