Hi Dave, Thanks agan for sticking with me on this. Before looking at your second solution, I have a question about your first solution. This account number always starts with a branch number, in this case it is 01. This report will always be for a particular branch, so it will start with 01 or 05, or 17. There's not always a leading zero. But when there is a leading zero, I need the leading 0 to be always just one zero. Would that happen with REPLICATE()? For instance, it looks like I could get the following from REPLICATE():
001234 012345 So the branch number would be 00 for one accont number and 01 for another, when they should all be 01. Am I right that this would happen with REPLICATE()? Meanwhile, I'll look at your other solution. Thanks Much, Lewis -----Original Message----- From: Dave Phillips <[EMAIL PROTECTED]> To: cf-newbie <[email protected]> Sent: Tue, 28 Oct 2008 5:43 pm Subject: RE: Numerical order of query results Lewis, I just thought of a few things. First, try this: ORDER BY REPLICATE( '0', 10 - LEN(a.Clco))+ a.Clco Replace 10 with a number that would be greater than your LONGEST string in ..Clco. This way, all your values will have leading zeroes and should sort ppropriately. If that doesn't work, Try this: ELECT * FROM ( SELECT ....your columns here, and add this to your select list: (CASE WHEN isNumeric(a.ClCo) THEN onvert(varchar(50),convert(numeric(8), a.ClCo)) ELSE a.ClCo END) as y_string (or whatever you want to call it) ROM ... HERE .... ) ORDER BY my_string kay, so here's what this does....The SELECT query inside the parenthesis ill be your regular query along with this extra column. The case statement ill look at a.ClCo and if it is numeric, then it will convert it to numeric which will get rid of leading zeroes) and then convert it back to varchar which will make it a string). If it's NOT numeric (01DEMO), it will just rovide that. So you either get a string of an account number without eading zeroes or the actual account number, if it's a string, in your new olumn (I called it my_string). Now, all of this has to be in your regular uery, and you need to wrap that entire query with SELECT * FROM ( ...your query here... ) ORDER BY my_string This will allow you to order by that new column you created which should ork because all values will now be a string. Now you should get the 01DEMO t the top since it has a leading zero, but everything else will NOT have he leading zero, so they should sort normally. Dave ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4106 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
