I'd guess it's because GROUP BY is in itself a distinct clause. You'd understand it better if there was another field being counted.
Ade -----Original Message----- From: Tim Laureska [mailto:hometeam@;goeaston.net] Sent: 18 October 2002 17:19 To: CF-Talk Subject: RE: apologies...query display balled up Andy .. using the ORDER BY event_no works, but can you help me understand why that eliminates the duplicates? -----Original Message----- From: Andy Ewings [mailto:andy.e@;thoughtbubble.net] Sent: Friday, October 18, 2002 11:56 AM To: CF-Talk Subject: RE: apologies...query display balled up I don't think this will do it as the distinct operator works on ALL of the columns combined in the select clause and in this case each is unique so you will still get the same data back. What you need is to run the same query as you have but with an order by like so... SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no ORDER BY event_no then continue using the group by in the cfoutput as you have..... -----Original Message----- From: Robert Polickoski [mailto:rpolickoski@;isrd.com] Sent: 18 October 2002 03:46 To: CF-Talk Subject: Re: apologies...query display balled up Tim, Use the keyword DISTINCT. I am guessing that because record #128 passes both tests, it is getting two result rows. I am not certain, but I think it will do the trick. Take care, ---------- Original Message ---------------------------------- From: "Tim Laureska" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Fri, 18 Oct 2002 11:02:36 -0400 >Man.. I apologize for how the first message came across formatted >weird.please let me try again: > >I have an event calendar access database with two tables (one called >category with two fields: "cat_no" and "descr".. the other table is >called events with many fields including two called "category" and >"category2") . Events can have one or two categories and each event >should be reflected on one row of the table with description, location >etc. The event_no is unique for each event > >The Table displays as shown below, but for example event# 128 should >only come up on one row with Fine Food & Beverage; Arts, Theatre & >Concerts shown under Category Description.... what's wrong with my code >below ?? > >Event no. Category Description LOCATION CATEGORY CATEGORY2 > >128 Fine Food & Beverage; St Michaels; 1 5 > > >126 Sports - Youth; St Michaels; 1 2 > >128 Arts, Theatre & Concerts; St Michaels; 1 5 > >126 Arts, Theatre & Concerts; St Michaels; 1 2 > > ><cfquery datasource="cal_endar_2" name="findcal"> >SELECT * >FROM events e, category c >where e.category=c.cat_no or e.category2=c.cat_no ></cfquery> > > ><table> ><cfoutput query="findcal" group="event_no"> ><tr> ><td valign="top">#event_no#</td> <td><cfoutput>#descr#;</cfoutput></td> ><td>#town#</td> ><td>#category#</td> ><td>#category2#</td> ></tr> ></cfoutput> ></table> > >Thanks in Advance >Tim Laureska > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com