[RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail

Sat, 07 Jul 2012 09:29:07 -0700

James:  'DISTINCT' has been variably included/omitted in my thrashings about. But point well taken: GROUP BY produces distinct sets.

Bruce
-------- Original Message --------
Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail
From: James Bentley <[email protected]>
Date: Fri, July 06, 2012 8:27 pm
To: [email protected] (RBASE-L Mailing List)

In the following command:
SELECT DISTINCT serialno,attrib1,attrib2,...,attribN +
 FROM table t2 +
 GROUP BY serialno,attrib1,attrib2,...,attribN +
 where serialno='12345'
  The key word DISTINCT should be omitted. The GROUP BY give distinct values.
I do not know if including that keyword will cause problems
Jim Bentley
American Celiac Society
[email protected]
tel: 1-504-737-3293
From: Bruce Chitiea <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Friday, July 6, 2012 4:52 PM
Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail

Rachel:

Back to the drawing board this weekend.

Thanks very much

Bruce


-------- Original Message --------
Subject: [RBASE-L] - Re: GROUP BY ... HAVING: Epic Fail
From: "Rachael Malberg" <[email protected]>
Date: Fri, July 06, 2012 1:52 pm
To: [email protected] (RBASE-L Mailing List)


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 -----
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 -----
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
>
>
>

Reply via email to