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" <[email protected]>
Sent: Monday, June 15, 2009 12:15 PM
To: "cf-talk" <[email protected]>
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:323517
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4