Did you set eqnull on before delete dups?
Sent from my Samsung Epic™ 4G Touch -------- Original message -------- Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail From: Bruce Chitiea <[email protected]> To: [email protected] CC: Bill: PROJECTed a temp table 'humpty'. Created an index on serialno. Command: DELETE DUPLICATES FROM humpty ...either as-is or with a USING collist results in: '0 row(s) have been deleted from humpty' There are a truck-load of duplicate rows. Snake-bit Bruce -------- Original Message -------- Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail From: "Bill Eyring" <[email protected]> Date: Fri, July 06, 2012 12:19 pm To: [email protected] (RBASE-L Mailing List) Bruce Try this. Create a temp table and insert all columns from the flat file into the temp table. Create an index on the serialno column. Then DELETE DUPLICATES FROM TEMPTABLE using col1,col2,col3,col4. you will be left with unique serial number rows for each attrib. Bill Eyring From: [email protected] [mailto:[email protected]] On Behalf Of Bruce Chitiea Sent: Friday, July 06, 2012 2:18 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail Albert/Lawrence: Both of the suggested constructs return a listing of all serialnos, including those having a count of 1. Perhaps a restatement is useful. The serialno column in this flat file from Hell is not a key, but just one value in duplicate rows. The serialno WILL become the primary key in the table to which it and related attributes will be extracted. My problem right now is that essential attribute values are randomly strewn across related rows so that some rows have all values, many do not, some have none and some have wrong values requiring review. I need ultimately to 'collect' and collapse all those values into one unique serialno'd row to insert into the new table. The list of serialno values I need to create will drive the cursor that will 'walk' the accursed flat file table. I'm not interested in knowing serialnos with multiple rows in this flat file from Hell. Serialnos whose attribute sets duplicate each other are ok. I'm looking for only those serialnos in rows containing variable attribute value sets. So this row set is NOT what I'm looking for: serialno attrib1 attrib2 attrib3 12345 | 100 | ABC | 200 12345 | 100 | ABC | 200 . 12345 | 100 | ABC | 200 Follows the jumbled row set I'm looking for: serialno attrib1 attrib2 attrib3 45678 | 100 | ABC | 200 45678 | 100 | XYZ | 200 45678 | -0- | ABC | 200 45678 | -0- | ABC | 100 ... and that's why I'm hoping that the GROUP BY ... HAVING structure can identify serialno values for row-sets of the 45678 variety while ignoring the 12345's. Thanks for hanging in there with this. Bruce -------- Original Message -------- Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail From: Albert Berry <[email protected]> Date: Fri, July 06, 2012 10:40 am To: [email protected] (RBASE-L Mailing List) Your group by clause will select all the rows, Bruce. Try this to get only those rows with duplicated serial numbers. SELECT SerialNo, Attrib1, Attrib2, AttribN + FROM table + WHERE SerialNo IN + (SELECT SerialNo from table group by serialno having count(*) > 1) On 06/07/2012 10:59 AM, Bruce Chitiea wrote: > Ok, I'm stumped. My generic code structure, the last of many I've tried: > > SELECT serialno + > FROM table + > WHERE serialno IN + > (SELECT DISTINCT serialno + > FROM table t2 + > GROUP BY serialno,attrib1,attrib2,...,attribN + > HAVING COUNT(*) > 1) > > > I'm cleaning up all the sin and normalizing from a legacy flat-file > database with several hundred thousand rows involving maybe 15,000 > unique 'serialno' values. > > I need to make a list of serialno values where for each listed serialno > there exist rows encompassing more than one unique set of > [serialno,attrib1,attrib2,...,attribN] values. > > No matter what I try, I end up with a list of ALL 15,000 distinct > serialno values. > > Help! > > Bruce > > > > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2012.0.2195 / Virus Database: 2437/5114 - Release Date: 07/06/12 > > >
