1

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.

Improve this question
2
  • 1
    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, 2012 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.
    – user7970
    Jun 17, 2012 at 14:56

1 Answer 1

Reset to default
3

Convert it to CHAR first:

ORDER BY CAST( AccounNumber AS CHAR )
Improve this answer
1
  • Superb, works like a charm! Never knew about this, thanks!
    – user7970
    Jun 17, 2012 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.