0

So I have a bunch of varchars that I need to sort in a certain way, first be alpha, and then treat the following number as a whole number (i.e. A2 would be before A10). That's the main problem, and then I have some with 'sub' letters after like A1M. Don't want to have to add a new column just for sorting, so I wanted to see if a more complex ORDER BY statement might be able to handle something like this.

They should be sorted like this:

A1
A1M
A2
A6
A6B
A9
A10
A15
A16
AS10

But it currently sorts like this (standard ORDER BY):

A1
A10
A15
A16
A1M
A2
A6
A6B
A9
AS10
Barmar
  • 741,623
  • 53
  • 500
  • 612
Bryant
  • 454
  • 4
  • 20
  • Sorry @Barmar but I dont think that answer OP question. Selected answer say `I think this is why a lot of things are sorted by release date.` – Juan Carlos Oropeza Oct 28 '15 at 21:26
  • The accepted answer doesn't do it, but some of the other answers do. – Barmar Oct 28 '15 at 21:28
  • The others rely upon a fixed position for the number, I have no idea where the number part will start – Bryant Oct 28 '15 at 21:33
  • @Barmar Im not sure if that is the way close questions should work. For example I know I can't call a duplicated if the question doesnt have a correct answer selected even when I know some answer are really good. So pointing duplicated to non correct answers in another post doesnt seem right. Instead you could use `share` function in the answer you think would help OP. – Juan Carlos Oropeza Oct 28 '15 at 21:48
  • 1
    See [this answer](http://stackoverflow.com/a/12257917/1491895). It shows how to find the position of the first digit in the column, then use that position to split the column into a prefix and suffix, so you can order by them separately. It would be better if you could fix the data to have the prefix and suffix in separate columns, this would allow them to be indexed. – Barmar Oct 28 '15 at 21:56
  • This is known as ‘natural order’. – Gumbo Oct 28 '15 at 21:58

1 Answers1

0

If the first character is a letter followed by a number, you can do:

order by left(col, 1),
         substr(col, 2) + 0,
         col

The first key is the first letter. The second is whatever number follows the letter. The + 0 is an implicit conversion that does not return an error. The final key is just the entire column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786