One step closer but A. Can't always assume that and if I could I think there might be case where there are more than 3 duplicates.
Might be I just have to bring back the list and order it by course_ID and let them chose to delete the ones the want. -- 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: "Ben Johansen" <[EMAIL PROTECTED]> > Organization: Intelisoft, Inc. > Reply-To: [EMAIL PROTECTED] > Date: Wed, 2 Jul 2003 15:52:35 -0700 > To: <[EMAIL PROTECTED]> > Subject: RE: Witango-Talk: Find duplicates > > 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 > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
