In this article I will show you how to order MySQL records by numeric values (numbers, integers & floats) from the smallest to the largest number or the other way around using a MySQL ORDER BY query.
A MySQL ORDER BY query with a given field name will order/sort the MySQL records alphabetically for the specified field name. If the field specified contains numbers, they will still be sorted alphabetically. Say we have 5 (five) records in the database table with the following values in the “price” field/column.
- 19.99
- 125
- 5.99
- 199
- 300
Lets execute a normal ORDER BY MySQL on the table and attempt to order/sort the records by the “price” field in an ascending manner. See the query below.
You might expect the query above to order records by price from the record with the smallest/lowest price in an ascending manner to the record with the biggest/highest price but this won’t necessarily happen. The records will be sorted with the “price” field in the following order.
- 125
- 199
- 19.99
- 300
- 5.99
As you can see the numbers are being ordered alphabetically and not as expected from the smallest/lowest number to the biggest/highest number. You can get the correct order as expected though by using the MySQL ABS modifier. It tells MySQL to process the field as an absolute numeric value. See the example below with the MySQL ABS modifier.
The result of using MySQL ABS are the records ordered with the “price” field from the lowest price to the highest price as expected. This can be used with any MySQL field/column which holds numeric values. You can use this on INT, BIGINT, FLOAT and other MySQL field types.
- 5.99
- 19.99
- 125
- 199
- 300







September 14th, 2009 at 12:52 pm
Thanks for the tutorial!
October 19th, 2009 at 11:32 pm
Thanks for the tips
October 21st, 2009 at 9:59 pm
You should try this:
SELECT * FROM `table` ORDER BY (`price` + 0) ASC;
Assuming that the data type is not numeric.
October 22nd, 2009 at 12:46 pm
Hey, thank you. I have searched for this a long time!
December 2nd, 2009 at 3:46 am
Hey thanks for the tip. Will try it out this weekend.
December 5th, 2009 at 2:44 pm
What? Why the hell would you store prices in a text column? That’s horrendously inefficient and foolish. Use the correct data type for the column in the first place.
January 15th, 2010 at 7:54 am
How do I connect to two MySQL databases at two different servers from a single php page?
February 7th, 2010 at 9:24 am
Many thanks for that My SQL Tip. I hope you will post again in the future.
March 5th, 2010 at 10:18 pm
How do I connect to two MySQL databases at two different servers from a single php page?
March 9th, 2010 at 8:27 am
There is nothing special in sorting by numbers. You can sort in same way by strings, dates or even enums.
Have a nice day )
March 13th, 2010 at 10:44 am
I come at this page while Google.. really helped a lot this tut.
March 31st, 2010 at 3:20 am
thanks very nice article