Invalid SQL - I don't think q of q handles the left function... -----Original Message----- From: Terry [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 3:08 PM To: CF-Talk Subject: RE: Sorting problem
Just a thought... If Access is the problem, why not try a q of q to resort? *********** REPLY SEPARATOR *********** On 6/5/02 at 2:57 PM Ian Lurie wrote: >Good idea, but that really got some weird results. My guess is Access just >can't handle any of this. > >-----Original Message----- >From: Rob Baxter [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, June 05, 2002 2:50 PM >To: CF-Talk >Subject: RE: Sorting problem > > >that weird. What about: > >order by Len(title), title > >that should group all the RD1-RD9 at the start then alpha sort them which >should give you the results you want. Might want to use the trim functions >inside the len(). > ></rob> > >-----Original Message----- >From: Ian Lurie [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, June 05, 2002 5:31 PM >To: CF-Talk >Subject: RE: Sorting problem > > >Tried that. Then it doesn't sort properly - it's still going RD1, RD10... > >-----Original Message----- >From: Rob Baxter [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, June 05, 2002 2:24 PM >To: CF-Talk >Subject: RE: Sorting problem > > >Instead of selecting it, why not just put the expression in the order by >clause? >i.e. > >order by Right(Title, Len(Title)-2) > >Then you don't have to worry about the aliasing. > ></rob> > > > >-----Original Message----- >From: Ian Lurie [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, June 05, 2002 5:04 PM >To: CF-Talk >Subject: RE: Sorting problem > > >This looks like a winner, except Access 2000 doesn't recognize the aliased >field for sorting purposes: > ><cfquery name="getdocs" datasource="#request.maindsn#" >cachedwithin="#request.cachetime#"> > SELECT d.*, right(title,len(title)-2) AS dsorter > FROM docs d, doc_cats_lookup dc > WHERE dc.category_id = #getyearcat.category_id# > AND d.isapproved = 1 > AND d.doc_uuid = dc.doc_uuid > ORDER BY dsorter ></cfquery> > >It can't find dsorter, even though I can list it out on the page. > >Ian > >-----Original Message----- >From: Raymond Camden [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, June 05, 2002 1:49 PM >To: CF-Talk >Subject: RE: Sorting problem > > >When you do your select, if your db support its, select >right(len(col)-2), this should return all of the info except for "RD". >You then want to cast it to integer and then sort in your order by >clause. On display, simply do: rd#col# > >======================================================================= >Raymond Camden, ColdFusion Jedi Master for Macromedia > >Email : [EMAIL PROTECTED] >Yahoo IM : morpheus > >"My ally is the Force, and a powerful ally it is." - Yoda > >> -----Original Message----- >> From: Ian Lurie [mailto:[EMAIL PROTECTED]] >> Sent: Wednesday, June 05, 2002 4:43 PM >> To: CF-Talk >> Subject: Sorting problem >> >> >> I've got a database of codes that go like this: >> RD1,RD2,RD3,RD4,RD5,RD6,RD7,RD8,RD9,RD10,RD11 and so on. >> >> Problem is, if you sort it, it ends up like this: RD1, RD10, >> RD11, etc. >> >> Any great, simple way to make sure that they sort the way the >> client wants: >> RD1, RD2...RD9,RD10? >> >> Ian >> >> Portent Interactive >> Helping clients build customer relationships on the web since 1995 >> Consulting, design, development, measurement >> http://www.portentinteractive.com >> Talk with us: http://projects.portentinteractive.com >> >> > > > > > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
