RE: Finding duplicate values in a column
From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicate values in a column
From: Mike Johnson From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! Oof. On re-reading this, I realized I wasn't entirely specific enough. What this'll return is all date_created values that have more than one record and a count of how many. The legwork after that is to select all the rows that have each of those date_created values, but that's not exactly a scalable solution. Come to think of it, I'm not entirely sure, off the top of my head, how to get the records themselves. Sorry for the confusion! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicate values in a column
No worries mate, that is exactly what I need, and the number I've got are 32 and when I subtract the ones more than a year old I've less than 10. I can do the legwork. :) Thanks. --- Mike Johnson [EMAIL PROTECTED] wrote: From: Mike Johnson From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! Oof. On re-reading this, I realized I wasn't entirely specific enough. What this'll return is all date_created values that have more than one record and a count of how many. The legwork after that is to select all the rows that have each of those date_created values, but that's not exactly a scalable solution. Come to think of it, I'm not entirely sure, off the top of my head, how to get the records themselves. Sorry for the confusion! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding duplicate values in a column
If you have an auto_increment or other unique recno you can do: select a.* from file a , file b where a.timefield = b.timefield and a.recno != b.recno Robert Reed wrote: No worries mate, that is exactly what I need, and the number I've got are 32 and when I subtract the ones more than a year old I've less than 10. I can do the legwork. :) Thanks. --- Mike Johnson [EMAIL PROTECTED] wrote: From: Mike Johnson From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! Oof. On re-reading this, I realized I wasn't entirely specific enough. What this'll return is all date_created values that have more than one record and a count of how many. The legwork after that is to select all the rows that have each of those date_created values, but that's not exactly a scalable solution. Come to think of it, I'm not entirely sure, off the top of my head, how to get the records themselves. Sorry for the confusion! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]