> OK, I changed the ORDER BY to Route_Name and Pick_Up and it is > ordering fine except for one school, the high school. What I am > getting is routes 10-23 first, then routes 6-9 are displaying. I know > what SQL Server is doing, it sees the number 1 in front of the other > numbers and is assuming that thsese are first, and on down the line. > So how do I accommodate this? Do I need to put a field in the table > that speficies which order each route is supposed to be?
Don't think that's necessary... With SQL server, you can specify an awful lot of data manipulation in the orderby, for example, I've used this in the past: order by case when colx = 'x' then 1 when colx = 'y' then 2 else 3 end that's just an extreme example ... in your case not very helpful because you don't have very well defined cases like that anyway, so what you need to do is massage the column that's being ordered into the appropriate format and it sounds to me like you can probably just get away with casting the route number to an integer, i.e. order by convert(route_name,int) (forgive me if I butchered that, I don't remember the order of the arguments for the convert function offhand) Of course, that won't work if you have route names with text in them -- in that case then you may need to fall back to adding a column just for sorting purposes, which you can populate with the number when the route info is saved. Given that iirc you're using SQL 2005, if you've got a strong stomach, you could use the Regular Expression features that were added in 2005 to strip out any non-numeric characters before converting the column to numeric in your order by clause. Looking at some info about it from Google it looks like the regex support in 2005 is actually a coincidental side-effect of SQL 2005 adding support for the .NET CLRs (don't quote me, I'm just skimming google) and it looks like you may want (or need) to create a user defined function to make it work. Here's my initial choice of reference: http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx hth ike -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 503.236.3691 http://onTap.riaforge.org ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295162 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
