I am using Access as a database and it does not support the PATINDEX function. Any
other thoughts?
Seva Petrov wrote:
> 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
