Or you could do that =) Either you update the data which allows better sorting or you inline query it with aggregate solutions and the data remains unsorted.
It is a philosophical choice. I like to keep good indexes, so I perform a lot of data scrubbing to avoid complex queries for other team members. On 1/25/07, Andy Matthews <[EMAIL PROTECTED]> wrote: > > <cfquery name="queryName"> > SELECT yourcolumn, 0+REPLACE(SUBSTRING_INDEX(yourcolumn,' ',1),',','') AS > sort > FROM yourtable > ORDER BY sort > </cfquery> > > -----Original Message----- > From: Chad Gray [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 25, 2007 8:13 AM > To: CF-Talk > Subject: SQL order by > > When you use ORDER BY in SQL on a text field it sorts the data like this: > > 1 > 11 > 12 > 13 > 2 > 21 > 22 > > Where I want > 1 > 2 > 11 > 12 > 13 > 21 > 22 > > I cannot switch it to an integer field because there will be some text > also. > > Any way to accomplish this? > > Thanks! > Chad > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267600 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
