Select distinct serialnum & attrib1 & attrib2 & attrib3, serialnum, attrib1, attrib2, attrib3 from table
Works in the database I have open at the moment. If there's a problem I guess you could do the concatenation in a column in the flat-file db and select distinct on that. Ben On Fri, Jul 6, 2012 at 11:17 AM, Bruce Chitiea <[email protected]> wrote: > 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 >> >> >> > >
