Actually, the prod_num IS an auto-increment field, so you're suggestion would work. It would be much better than updated, especially since I found out that someone was busily updating the old products after the new ones were put in. I just started down the wrong road and never rethought the matter. I will ponder your solution.
I was starting with a list that just had the titles on it, that's why I have to use that as the check. In the event, I had the list of titles in an Excel spreadsheet, so I used macros to build a SELECT for each title. That was ugly, but at least it gave me the data. Thanks for your help. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 12, 2006 2:14 PM > To: Jerry Schwartz; mysql@lists.mysql.com > Subject: Re: Finding duplicates > > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
