Page 1 of 1

ORDER BY NUMBER 1,10,11 incorrect

Posted: Wed Aug 20, 2014 11:14 am
by jonnyfortis
i want to order an item by the number in the database field 1,2,3,4,5,6 iup to 15 but it is displaying 1,10,11 and up which i dont want

ORDER BY beau_products.order ASC

the order column is varchar (10)

Re: ORDER BY NUMBER 1,10,11 incorrect

Posted: Wed Aug 20, 2014 12:00 pm
by Celauran
It's using string ordering because you're storing numbers as strings. Don't do that. Use INT, SMALLINT, TINYINT, etc. Use the right column type for your data.

Re: ORDER BY NUMBER 1,10,11 incorrect

Posted: Wed Aug 20, 2014 12:03 pm
by Celauran
In the interim you can use CAST, but you really want to correct this.

Code: Select all

SELECT foo FROM table_name ORDER BY CAST(`order` AS UNSIGNED) ASC

Re: ORDER BY NUMBER 1,10,11 incorrect

Posted: Wed Aug 20, 2014 1:35 pm
by jonnyfortis
Celauran wrote:In the interim you can use CAST, but you really want to correct this.

Code: Select all

SELECT foo FROM table_name ORDER BY CAST(`order` AS UNSIGNED) ASC
thanks. i will change the column to int, thanks again