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