Order By with letters and numbers?

[Petty_Crim]

I'm trying to make a query that orders descending on a field that has a mix of letters and numbers. The fields are like this E-(a number) ie E-1. The problem is though when it gets to E-10 it put that ahead of E-9, it seems to treat E-10 as E-1.

 

It should go like this:

E-10

E-9

E-8

so on

 

atm its doing

E-9

E-8

...

E-1

E-10

Anyone know how to get around this?

[Orio]

I am no SQL expert, but I don't know if there's a way to order them this way. The only solution I can think of is if you fetch all the data and store it in an array, and then apply natsort() on it.

 

Orio.

[rajivgonsalves]

You could use substr with with instr in mysql to get split it say E and then 1 then order accordinly but I would suggest you stored it in the db like this

 

E-01

E-02..

E-10

 

or if you have more than 3 digits the more zero it would sort properly

[aschk]

Your problem exists because your table column is of type VARCHAR, and in ASCII and language terms E-1(anything) comes before E-9.

It works letter by letter  (because you're working with characters NOT numbers).

Character1 | Character2 | Comparision
=============================
E              | E              | EQUAL
-              | -               | EQUAL
1               | 9              | 1 is LESS than 9 , now we have our ordering...

 

The answer to your problem is to segregate your field into two fields.

A char field (myCharField) and an int field (myIntField). In the CHAR field you have "E" and in the INT field you have 10 or 9 or whatever number you fancy. Then you can do ORDER BY myCharField, myIntField

Then you will have your ordering correctly.