Frederic,
You can try using PATINDEX to set a bit on the matching town and then order
on the results of the PATINDEX, which will return the town with the bit set
first:
<cfquery name="get_1" datasource="#DS_1#" dbtype="ODBC">
SELECT PATINDEX('#RMOTOWN#', T.RMOTown) as pmatch,
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 pmatch DESC, by T.rmotown, A.PrimaryName
</cfquery>
HTH,
Seva Petrov
> 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
