1

I currently have a simple MySQL select with an order by on a column called postcodes. These postcodes are UK.

It is currently outputting in this order: SE1, SW1, SE10, SE11, SE2, SW2, SE3. I know using ABS() would correct the number ordering, but I'm not sure how I would do it in this case because there are letters as well as numbers.

I would like to display them in the following order:
SE1, SE2, SE3 SE10, SE11, SW1, SW2

Thanks for any help.

Ben
  • 51,770
  • 36
  • 127
  • 149
Sickaaron
  • 448
  • 1
  • 12
  • 21
  • possible duplicate of [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) – Marc B Mar 28 '12 at 18:00
  • 1
    You forgot SW1W, EC1A etc... which'll cause more problems... GiroBank GIR 0AA, BFPO... so you can't even just use numbers. It'd probably be better to do it by distance than anything else. – Ben Mar 28 '12 at 20:08

1 Answers1

2

You could add a few extra columns to the table to contain the snippets within them and return the order by using the tertiary columns to sort by. For example:

PostCodeTable
|---------------------------------------------|
|postCode| codePart1 | codePart2 | codePart3  |
|char()  | char()    | int()     | whatever() |
|---------------------------------------------|
| SE1    | SE        | 1         |            |
| SW1    | SW        | 10        |            |
| SE10   | SE        | 10        |            |
| SE11   | SE        | 11        |            |
| SE2    | SE        | 2         |            |
| SW2    | SW        | 2         |            |
| SE3    | SE        | 3         |            |
|---------------------------------------------|

Your query might be:

Select
    postCode
from
    PostCodeTable
order by
    codePart1,
    codePart2
    // etc etc as needed.
Nikola K.
  • 7,093
  • 13
  • 31
  • 39
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • How does this solve the problems of the districts that were [sub-divided](http://en.wikipedia.org/wiki/London_postal_district#High-density_districts) because of the number of delivery points? SW1A, SW1E, SW1H etc. – Ben Jul 07 '12 at 10:24
  • @Ben I don't know how many combinations of letters+numbers+letters+numbers there are (hence the codePart3), but if you split them into individual groups, you can easily sort by them and return the main part in correct order. – Fluffeh Jul 07 '12 at 10:29