Well... Jason.... ID and SITE will always have unique values.... no duplicates allowed. Other than that, CLUSTER field can have duplicate values. In simple English if I phrase it, "Multiple SITEs can belong to a SINGLE CLUSTER" and that's why I need to GROUP BY cluster.
It's basically a Telecom data management project :)) (That is why it has to be so mismanaged ;)... lolz!) -------------------------------------------------- From: "Jason Fisher" <ja...@wanax.com> Sent: Monday, June 15, 2009 11:08 PM To: "cf-talk" <cf-talk@houseoffusion.com> Subject: Re: Case Sensitive SQL > > Right, include all the columns, but also > > GROUP BY cluster > > not > > GROUP BY clust > > In other words, group on the actual column name, not the alias. > > Now, if your goal is simply to get only 1 of every record combination, > since you're not using any other Aggregate functions besides GROUP BY, why > not just use DISTINCT? > > SELECT DISTINCT id, site, far_end, site_type, phase, lat, long, cluster AS > clust, city, region, sites_status > FROM sites > WHERE site IS NOT NULL > AND siteID1 = '#UCASE(Rereplacenocase(form.site,"-| > ","","ALL"))#' > AND siteID2 = '#UCASE(Rereplacenocase(form.farend,"-| > ","","ALL"))#' > IS NOT '0'>AND site_type = #form.tos# > form.region IS NOT '0'>AND region = #form.region# > form.cluster IS NOT '0'>AND cluster = #form.cluster# > form.phase IS NOT '0'>AND phase = #form.phase# > form.city IS NOT '0'>AND city = #form.city# > ORDER BY region > > ---------------------------------------- > From: "Jochem van Dieten" <joch...@gmail.com> > Sent: Monday, June 15, 2009 12:15 PM > To: "cf-talk" <cf-talk@houseoffusion.com> > Subject: Re: Case Sensitive SQL > > On Mon, Jun 15, 2009 at 4:56 PM, Arsalan Tariq Keen wrote: >> >> � � � �SELECT id, site, far_end, site_type, phase, lat, long, >> cluster AS >> clust, city, region, sites_status >> � � � �FROM sites > >> � � � �ORDER BY region >> >> >> >> now if I add 'GROUP BY clust' then it gives an error. > > Don't forget that every column that is in your SELECT list should > either be an aggregate statement or should be in the list of columns > in the GROUP BY. > > Jochem > > -- > Jochem van Dieten > http://jochem.vandieten.net/ > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323520 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
