curious but does this query have the correct returns?
SELECT DISTINCT serialno +
> FROM table t2 +
> GROUP BY serialno,attrib1,attrib2,...,attribN +
> HAVING COUNT(*) > 1)
Rachael M
Freelance Developer
(218) 999-9689
www.DragonflyDevelopmentMN.com
----- Original Message -----
From: Bruce Chitiea
To: RBASE-L Mailing List
Sent: Friday, July 06, 2012 1:17 PM
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
>
>
>
