order by cast(rtrim(ltrim(Right(Title, Len(Title)-2))) AS INT) - j
-----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
