Ben: Easy to do. I'll give it a shot tomorrow. Thanks, Bruce
-------- Original Message --------
Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail
From: Ben Petersen <[email protected]>
Date: Fri, July 06, 2012 12:14 pm
To: [email protected] (RBASE-L Mailing List)
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
>>
>>
>>
>
>
