Oh, and my solution is for MS SQL 2000 ... -----Original Message----- From: Jenny Gavin-Wear [mailto:[EMAIL PROTECTED] Sent: 12 April 2008 01:00 To: CF-Talk Subject: RE: Order by question
Hi Paul, You can make use of CONVERT (or CAST, I think). For example: The table to be sorted here has two columns, the ID and the values to be sorted: SELECT TOP 100 PERCENT tempID, CONVERT(varchar, NumValues) AS TextValues FROM dbo.tbl_temp ORDER BY CONVERT(varchar, NumValues) In your code it would be: <cfquery name="get_PH" datasource="xxx"> SELECT convert(varchar, hier_num) as TextValues, .hier_desc FROM hierarchy ORDER BY CONVERT(varchar, hier_num) </cfquery> Hope this helps, Jenny -----Original Message----- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: 03 April 2008 21:38 To: CF-Talk Subject: Order by question if i have a bunch of numbers that i want to order like this in a drop dowm, how do i do it? the origional order by id was fine when the list was small.. but now it has grown. forcing it to look the way we want below is boggeling.. 1 101 101010 2 201 202 201010 201020 3 302 et....... <cfquery name="get_PH" datasource="xxx"> SELECT hier_num, .hier_desc FROM hierarchy </cfquery> if i order by hier_num it just does as expected 1 2 3 101 201 301 et.... as always i appreciate your help. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303222 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4