You're kind of heading down the right road. And this was discussed on the list
not too long ago, how to delete duplicates.
Here's one solution that will find the oldest duplicate(s):
SELECT prod.prod_num,prod.prod_title,prod.updated FROM prod
JOIN (
SELECT prod_title,max(updated) maxdate
FROM prod GROUP BY prod_title ) AS proda
ON prod.prod_title=proda.prod_title AND prod.updated!=proda.maxdate
That should show you all the duplicates. Although product title and date are not good values to check for duplicates on. If you have
one product that has two records with the same date, this query won't find it because the record are exactly the same. You're
prod_num is not specified as auto_increment, so you couldn't use that for uniqness. If it was auto_increment, then you know the
latest record with have the highest number, then you would use that instead of updated.
----- Original Message -----
From: "Jerry Schwartz" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, October 12, 2006 1:42 PM
Subject: Finding duplicates
It seems to me I ought to be able to construct a query, probably using
sub-SELECTs), that would do what I want. I have a table that looks roughly
like this:
CREATE TABLE prod (prod_num INTEGER UNIQUE, prod_title VARCHAR(255), updated
DATE)
UNIQUE PRIMARY KEY (prod_num);
In this table there might be two records that have the same title, with
different values for prod_num and updated. I want to find the prod_nums for
the older of the two records. I know when the new ones were updated, so that
can be a literal.
I just haven't been able to think it through. Using NOT IN with the subquery
gave me the inverse of what I wanted. This shouldn't be impossible to do,
Access has a "find duplicates" wizard; but come to think of it, that lists
both records and I never tried to limit it to just the older.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
--
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]
