Lost-In-Code

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