Augh. I get RD2,RD1,RD6,RD3,RD5... -----Original Message----- From: Rob Baxter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 3:17 PM To: CF-Talk Subject: RE: Sorting problem
Okay, I just tried this in access. Very strange. Try this Select *, Len(Title) . Order By Len(Title), Title In Access 2000 it seems to work if the expression you are sorting on is also in the select clause. Otherwise it does the Len sort but not the alpha sort. See if it works for you. </rob> -----Original Message----- From: Ian Lurie [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 5:57 PM To: CF-Talk Subject: RE: Sorting problem 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 > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm 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
