List:General Discussion« Previous MessageNext Message »
From:Bruce Feist Date:April 2 2003 12:52am
Subject:Re: Opposite of DISTINCT()
View as plain text  
Jennifer Goodie wrote:

>Why the join?  Why not just "select p1.email, count(*) as occurances from
>table p1 group by p1.email having occurances > 1"?  Am I missing something?
>
Possibly.  It depends on whether the OP wanted to see which rows had 
duplicates, or to actually *see* the duplicates, in which case a join 
would be needed.  (It would not, however, be quite the join shown 
below... one query would find the rows with duplicates and yield a 
temporary table, and then the second would join that table back to the 
original to find the details.

Bruce Feist

>
>  
>
>>-----Original Message-----
>>You have to do a self join - try this off the top of my head... -
>>
>>Select p1.email
>>FROM tblperson p1, tblperson p2
>>WHERE p1.email = p2.email
>>GROUP BY p1.email
>>HAVING count(p1.email) > 1
>>    
>>



Thread
Opposite of DISTINCT()Bob Sawyer1 Apr
  • RE: Opposite of DISTINCT()Michael Shulman1 Apr
  • RE: Opposite of DISTINCT()Andy Eastham1 Apr
    • RE: Opposite of DISTINCT()Jennifer Goodie2 Apr
      • Re: Opposite of DISTINCT()Bruce Feist2 Apr
  • RE: Opposite of DISTINCT()Kevin Fries2 Apr
RE: Opposite of DISTINCT()Bob Sawyer2 Apr