Okay, Lewis. I got your table, now I understand a bit better. Try this:
ORDER BY SUBSTRING(a.Clco,1,2)+REPLICATE('0', 10 -
LEN(SUBSTRING(a.Clco,3,LEN(a.Clco)))+ SUBSTRING(a.Clco,3,LEN(a.ClCo))
Okay, that looks crazy, but here's what it SHOULD do (and remember, these
values are ONLY for sorting - this will NOT change how your account numbers
are displayed).
Take the first two digits, which is the branch, then add zeros (to 10
places, for scalability purposes) to the beginning of the account number,
then add the rest of the account number.
So, your initial sort (abbreviated here):
0110192
0110193
0110194
011070
011103
011138
01DEMO
Should look like this (for the ORDER BY operation only):
010000010192
010000010193
010000010194
010000001070
010000001103
010000001138
01000000DEMO
Now, they should sort properly and display on your page like this:
010000001070
010000001103
010000001138
01000000DEMO
010000010192
010000010193
010000010194
Let me know if that works.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2008 1:58 PM
To: cf-newbie
Subject: Re: Numerical order of query results
Did you get the email with the table I sent?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:4118
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
