Hi Dan, Since aggregates are allowed on the left side of the group by with a grouping reference you might try the following
> select course_id, max(record_id) > from tablename > where staff_id = 'staff_id_value' > group by course_id > having count(course_id) > 1; This assumes that out of 2 entries the first one is the one you want to keep. If you want to delete the first record_id of the entries then use min(). Ben Johansen - http://www.pcforge.com Authorized Witango Reseller http://www.pcforge.com/WitangoGoodies.htm Authorized MDaemon Mail Server Reseller http://www.pcforge.com/AltN.htm -----Original Message----- From: Dan Stein [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 3:40 PM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: Find duplicates Jeff, Thanks SQL works fine it brings back a list of the records that are duplicates but only if I don't include the record ID so there is no way then delete the duplicates. Here is the overall issue. Due to some missing traps and changes to the way courses were created it is possible for a person to use the back button on the browser and register twice for a course. I have fixed this but for the current record set and if they find another way to break the check I need an admin function so they can remove duplicate records. I was hoping I could bring back a list of the course registrations for a person and then from that list let the admin person decide what to delete. I know I could bring back the whole list of courses and then filter it in a for loop so it only shows duplicates but it would be so much easier to just grab the duplicates in the DBMS or Search action and be able to just display the results. Dan -- Dan Stein Digital Software Solutions 799 Evergreen Circle Telford PA 18969 Land: 215-799-0192 Mobile: 610-256-2843 Fax 413-410-9682 FMP, WiTango, EDI,SQL 2000 [EMAIL PROTECTED] www.dss-db.com > From: Jeffrey Bohmer <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Wed, 2 Jul 2003 14:32:10 -0600 > To: [EMAIL PROTECTED] > Subject: Re: Witango-Talk: Find duplicates > > > Here's the SQL: > > select course_id > from tablename > where staff_id = 'staff_id_value' > group by course_id > having count(course_id) > 1; > > (Sorry, I'm not sure how that would look in a search action.) > > - Jeff > > > > >> Looking for an easy way to bring back a list of duplicate course ID's for a >> given staff_ID in a table based on a search action along if possible >> >> SQL 2000 >> -- >> Dan Stein >> Digital Software Solutions >> 799 Evergreen Circle >> Telford PA 18969 >> Land: 215-799-0192 >> Mobile: 610-256-2843 >> Fax 413-410-9682 >> FMP, WiTango, EDI,SQL 2000 >> [EMAIL PROTECTED] >> www.dss-db.com >> > -- > > Jeffrey Bohmer > VisionLink, Inc. > _________________________________ > 303.402.0170 > www.visionlink.org > _________________________________ > People. Tools. Change. Community. > ________________________________________________________________________ > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
