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
September 29th, 2010 at 8:57 pm
Thanks for the tips! that was just what I needed!
January 2nd, 2011 at 11:44 am
In this article I will show you how to order MySQL records by numeric values (numbers, integers
February 1st, 2011 at 11:04 am
Wow! This even works on text fields that include numbers and text characters together!
For example, my field was able to sort numbers even when I had values as such:
230-Medium
440-High
110-Low
Thanks for the example!
February 8th, 2011 at 12:43 pm
Thanks! Saved me a ton of research, trial and error – trying to sort numerically on a longtext field. Worked like a charm.
February 28th, 2011 at 3:19 am
pls tell me how to concatinate two number fields in mysql.
June 10th, 2011 at 8:28 am
thx man from Brasil!
June 16th, 2011 at 6:02 pm
thx for code
August 31st, 2011 at 2:34 pm
Thanks for the example.
Another way is to use the +0 option (although I understand it is a bit slower), but it sometimes works when ABS() wont. I had an problem where we had been numbering our issues as volume.issue_number.
For example. 1.1 is the first issue of the first volume and 1.10 is the tenth issue of the first volume. The proper way would have been to get them to use .01, but let’s face it… we don’t always thing that way in advance, so I was in a bind! I tried ABS and it didn’t work in this situation for some reason, so I turned to +0.
What I discovered after a lot of research is that you can do something fancy by using the +0 option combined with SUBSTRING_INDEX and ORDER BY to split a field during the query.
SELECT DISTINCT issue FROM tbl_pages
WHERE isActive=1
ORDER BY
(SUBSTRING_INDEX(issue, ‘.’, 1)+0),
(SUBSTRING_INDEX(issue, ‘.’, -1)+0)
This query first orders the issues numerically by the volume number in the field and then orders the issue numerically by issue number. SUBSTRING_INDEX is recognizing the period as a delimiter and
breaking up the column, in order to sort correctly.
Hope this helps someone.
September 4th, 2011 at 10:41 pm
hi all
my problem is similar to fictus but unfortunately the “first” number can be 1,2 or 3 digit(s).
how can i sort in right way?
for example:
1mm N52 NEODYM
10mm N52 NEODYM
100mm N52 NEODYM
2mm N52 NEODYM
3mm N52 NEODYM
what i need:
1mm N52 NEODYM
2mm N52 NEODYM
3mm N52 NEODYM
10mm N52 NEODYM
100mm N52 NEODYM
October 17th, 2011 at 5:34 am
Thank you very much this helps me a lot. i almost give up with mysql query until i saw this article ….muhammed sekertekin
November 16th, 2011 at 8:42 am
thank you so much for solving my issue…
December 9th, 2011 at 9:46 pm
Thanks for the tutorial!