# How to order by 5, 501, 50101, 6, 601, 60101, etc?

I've got a list of ledger account numbers. These numbers go like this:

• 5 (main account, #5)
• 501 (sub account of main account 5, #01)
• 50101 (sub account of this account, #01)
• etc.

So if I have a list and want to order this by account number, then I'd like to create this order:

• 5
• 501
• 50101
• 50102
• 50103
• 6
• 601
• 60101
• 60102
• 60103

etc.

However, any ORDER BY will give me:

• 5
• 6
• 501
• 601

etc.

So what is the proper way of working with this kind of numbering? I imagine I can get another column for ordering, but I really prefer to use the account numbers as IDs that I use for ordering, as not to make things too complicated.

-
Well, you could consider storing this data separately. What are you gaining by munging them together? If you store them separately, you can always order by a sum of the parts. – Aaron Bertrand Jun 16 '12 at 20:46
What I gain is simplicity. I will be needing this account number very often, but only infrequently do I need to order the list. So storing this separately makes things more complicated for only one specific, uncommon use. – user Jun 17 '12 at 14:56

Convert it to `CHAR` first:
``````ORDER BY CAST( AccounNumber AS CHAR )