Enhancing an 'ORDER BY' clause to judge condition by more than 1 integer

Question

Hi folks,

I have some PHP code which allows me to sort a column into ascending and descending order (upon click of a table row title), which is good. It works perfectly for my D.O.B colum (with date/time field type), but not for a quantity column.

For example, I have quantites of 10, 50, 100, 30 and another 100.

The order seems to be only appreciating the 1st integer, so my sorting of the column ends up in this order: 10, 100, 100, 30, 50... and 50, 30, 100, 100, 10.

This is obviously incorrect as 100 is bigger than 50, therefore both 100 values should appear at the end surely? It seems to me that 100 is only being taken into account as having the '1' value, then it appears before 10 because the system recognises it has another 0.

Is this normal to happen? Is there any way I can solve this problem? Thanks for any help. P.S. I can show code if necessary, but would like to know if this is a common issue by default.

Answer 1

Your field is probably defined as char/varchar/text/..., i.e. as string type and not a numeric type. In that case lexicographical sorting performed.

Answer 2

Sounds like you're sorting by considering those values as strings, instead of as numbers. What's doing the sorting? PHP, or MySQL? Hard to say what's wrong without seeing the code that's actually sorting it.

Answer 3

The sort is treating your data as strings instead of integers. Check the datatype of your column in the database. If that is a number and not a string, then the data is being converted to strings somewhere between the database and the sort routine.

Answer 4

looks like you are sorting strings and not numbers, what is the data type of the column? The only way to sort string numbers properly is to add leading zeros, but it is best to store that data in the proper type, so you don't have to do these sort of manipulations each time you select the data.

Answer 5

In the event that you need to leave the column as a character type for some reason, you can cast to a suitable numeric type for the ordering. This is less efficient than just changing the column though.

ORDER BY CAST(theColumn AS DECIMAL(5, 2)) 

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Answer 6

Your column is a text type. You can change the column type permanently, or cast it when you do the sort.

Table alter (you may want a different default value)

 ALTER TABLE [Table]
MODIFY COLUMN [Column] INTEGER UNSIGNED NOT NULL DEFAULT '0';

Or cast when you sort

SELECT *
  FROM [Table]
 ORDER BY CAST([Column] as UNSIGNED INTEGER) ASC;