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

Reply via email to