Thanks that did the trick.
"Howarth, Craig (IBK-NY)" wrote:
> 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
