[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...

[End User]

Actually I believe the sort orderis correct. You may need to specify an ASCII sort.

Originally Posted by 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...

[mab]

This is not really a bug, it is how you have defined and stored your data.

Because the item numbers have an alphabetic character, they are no longer numbers. They are strings and I am sure your column type is a character/string type.

The reason for the "incorrect" order, is this is how strings are sorted, character by character, left to right. That they have numbers in them does not matter and has no meaning.

There are a number of ways you can fix this -

1) Keep them as strings, but make all the number fields the same length -
c01, c02, ... c09, c10, c11... This makes the same character position in each string have the same significance.

2) Remove the "c" from your item numbers and make the column an actual number type. You can also remove the "c" and leave this as a character type, but you must force the query to treat them as numbers to get it to sort correctly. If you want the "c" in the final results, you can either append it as part of the query or as part of the code that processes the results.

3) You can form a query that strips the "c" and converts this to a number to be used in the ORDER BY clause. The following query will work (tested) -

PHP Code:

$query = 'SELECT itemno FROM your_table ORDER BY CONVERT(REPLACE(itemno,"c",""),UNSIGNED)';