Try this:
I have added an additional column which test for the value of RMOTown and outputs a 0
if it matches otherwise a 1. The same column is added to the order by. This should
work in Access.
<cfquery name="get_cluster" datasource="#DS_1#" dbtype="ODBC">
SELECT T.RMOTown, T.Cllustername
FROM RMOTownName T
WHERE T.RMOTown='#RMOTOWN#'
</cfquery>
<cfquery name="get_1" datasource="#DS_1#" dbtype="ODBC">
SELECT iif(T.RMOTown='#RMOTOWN#',0,1) as FirstSort,
T.RMOTown as town, T.Chamberwebaddress as webad,
T.Cllustername, A.PrimaryName as pname,
A.Description as descipt,
a.PhysicalAddress +', '+a.City +', '+a.State +', '+a.ZipCode as
address,
a.Phone as phone, a.TollFreePhone as tfphone,
EMailAddress as email, a.WebSite as web
FROM RMOTownName T, Attractions A
<cfif LEN(trim(get_cluster.Cllustername)) gt 0>
WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName
WHERE RMOTown='#RMOTOWN#')
AND A.RMOTown=T.RMOTown
<cfelse>
WHERE T.RMOTown='#RMOTOWN#'
AND A.RMOTown=T.RMOTown
</cfif>
order by iif(T.RMOTown='#RMOTOWN#',0,1), T.rmotown, A.PrimaryName
</cfquery>
> -----Original Message-----
> From: Freddy [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, March 19, 2001 6:50 PM
> To: CF-Talk
> Subject: QUERY TROUBLE
>
> I have a query that returns a list of attractions in a certain town. If the town is
>in a "cluster" (a group of
> related towns) it also needs to order by the selected town first and then list the
>rest of the towns
> alphabetically.
> How would I get this output from the following queries?
>
> <cfquery name="get_cluster" datasource="#DS_1#" dbtype="ODBC">
> SELECT T.RMOTown, T.Cllustername
> FROM RMOTownName T
> WHERE T.RMOTown='#RMOTOWN#'
> </cfquery>
>
> <cfquery name="get_1" datasource="#DS_1#" dbtype="ODBC">
> SELECT T.RMOTown as town, T.Chamberwebaddress as webad,
> T.Cllustername, A.PrimaryName as pname, A.Description as descipt
> ,a.PhysicalAddress +', '+a.City +', '+a.State +', '+a.ZipCode as
> address, a.Phone as phone, a.TollFreePhone as tfphone,EMailAddress
> as
> email, a.WebSite as web
> FROM RMOTownName T, Attractions A
> <cfif LEN(trim(get_cluster.Cllustername)) gt 0>
> WHERE Cllustername IN (SELECT Cllustername FROM RMOTownName
> WHERE
> RMOTown='#RMOTOWN#') AND A.RMOTown=T.RMOTown
> <cfelse>
> WHERE T.RMOTown='#RMOTOWN# ' AND A.RMOTown=T.RMOTown
> </cfif>
> order by T.rmotown, A.PrimaryName
> </cfquery>
> Is there something I can do in the ORDER BY clause that will allow for this?
> I know I can use an if statement in the output to output the selected
> town's info first but this will probibaly mess with the way my pagination through
>the results works and is not the
> most efficient
> method.
> Thanks,
> Frederic.
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
