then there is something diff in that data for your '12345 | 100 | ABC | 200'
recs.
do a
SELECT DISTINCT serialno,attrib1,attrib2,...,attribN +
> FROM table t2 +
> GROUP BY serialno,attrib1,attrib2,...,attribN +
> where serialno='12345'
and find your faulty recs
12345 | 100 | ABC | 200
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 3:32 PM
Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail
Rachel:
This clause produces a result in the right direction - in that it groups rows
by attributes; but falls short of the goal because the grouping includes all of
the
12345 | 100 | ABC | 200
12345 | 100 | ABC | 200
...row sets as well. There are a lot of those.
I guess what I'm looking for is for the GROUP BY...HAVING clause to produce:
serialno attrib1 attrib2 attrib3
45678 | 100 | ABC | 200
45678 | 100 | XYZ | 200
45678 | -0- | ABC | 200
45678 | -0- | ABC | 100
... because there's more than one attrib set to group by, so that the routine
returns only '45678', ignoring '12345'.
Bruce
-------- Original Message --------
Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail
From: "Rachael Malberg" <[email protected]>
Date: Fri, July 06, 2012 12:47 pm
To: [email protected] (RBASE-L Mailing List)
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
>
>
>
