Re: Opposite of DISTINCT()
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opposite of DISTINCT()
Question answered. Thanks to all who responded. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opposite of DISTINCT()
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? > -Original Message- > From: Andy Eastham [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 01, 2003 12:29 PM > To: [EMAIL PROTECTED] Mysql. Com > Subject: RE: Opposite of DISTINCT() > > > Bob, > > 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 > > Andy > > > -Original Message- > > From: Bob Sawyer [mailto:[EMAIL PROTECTED] > > Sent: 01 April 2003 21:04 > > To: MySQL List > > Subject: Opposite of DISTINCT() > > > > > > I know that using SELECT DISTINCT(colname) will result in > output that does > > not contain any duplicates from that column. But how would I > > output JUST the > > duplicates? If I have as part of a table a column containing email > > addresses, and I want to list just the duplicate addresses > rather than the > > distinct addresses, what's the syntax there? > > > > Thanks, > > Bob > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opposite of DISTINCT()
To find duplicates, use something like: SELECT address, count(*) >From Customer GROUP BY address HAVING count(*) > 1; -Original Message- From: Bob Sawyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 12:04 PM To: MySQL List Subject: Opposite of DISTINCT() I know that using SELECT DISTINCT(colname) will result in output that does not contain any duplicates from that column. But how would I output JUST the duplicates? If I have as part of a table a column containing email addresses, and I want to list just the duplicate addresses rather than the distinct addresses, what's the syntax there? Thanks, Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opposite of DISTINCT()
Bob, 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 Andy > -Original Message- > From: Bob Sawyer [mailto:[EMAIL PROTECTED] > Sent: 01 April 2003 21:04 > To: MySQL List > Subject: Opposite of DISTINCT() > > > I know that using SELECT DISTINCT(colname) will result in output that does > not contain any duplicates from that column. But how would I > output JUST the > duplicates? If I have as part of a table a column containing email > addresses, and I want to list just the duplicate addresses rather than the > distinct addresses, what's the syntax there? > > Thanks, > Bob > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opposite of DISTINCT()
SELECT col1 FROM table1 GROUP by col1 HAVING count(col1) > 1 -ms -Original Message- From: Bob Sawyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 12:04 PM To: MySQL List Subject: Opposite of DISTINCT() I know that using SELECT DISTINCT(colname) will result in output that does not contain any duplicates from that column. But how would I output JUST the duplicates? If I have as part of a table a column containing email addresses, and I want to list just the duplicate addresses rather than the distinct addresses, what's the syntax there? Thanks, Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
