Mysql Order by bug

[DjNaF]

Hi,

 

I have a DB with lots of property information in it and I need to return all the properties and order them by price. However, I at the moment my query is sorted incorrectly:

 

c1

c10

c11

c12

c2

c20

c21

c3

c31

 

They should be more like:

c1

c2

c3

c11

c12

c20

c21

c31

How would I go about this? My existing query is as follows:

 

Code:

SELECT * FROM <table> ORDER BY itemno ASC

 

Thanks in advance...

 

[toplay]

This should work but it assumes there's always going to be just one alphabetic character in the first position. The "+0" forces the value to be numeric.

 

SELECT

              LEFT(itemno, 1) AS item_alpha

            , SUBSTRING(itemno, 2) + 0 AS item_number

            , tb.*

  FROM

            table_name tb

ORDER BY

            item_alpha ASC, item_number ASC

 

[artacus]

Yeah, I don't know if you are in a position to change your setup but it would be better if you could use 'C01' (or 'C001'  if you are going to have more than 99 properties.

[btherl]

The reason for the unexpected ordering is that varchar data is sorted as strings, not as numbers.  String sorting doesn't align the units, tens and hundreds, which is why you get the strange sort order you see.

 

The solutions already mentioned will work.  Another alternative is to use an integer type for your property names.  Integers will be sorted in the way you expect, with 1 < 2 < 3 < ... < 10 < 11 < ...