Normally when you want to sort numerically on a column in your database you’d make the column some sort of numeric type such as an int. Sometimes, however, you are stuck with someone else’s schema and they have decided to store numbers in a text type column and you need to sort your results numerically because an alphabetic sort on a number does not produce the results you want. There is a quick trick to that makes this easy.
The Solution
Put a +0 after the column name in your Order By clause like this.
SELECT ColumnName FROM TableName ORDER BY TextColumnName+0 ASC;
Real Life Example
Suppose you are working in WordPress with a custom post type that stores a numeric value like “price” in the WordPress postmeta table in the meta_value column which happens to be of type LONG_TEXT. Here is a query that will let you select all the custom posts and sort them by price in descending order.
SELECT posts.*, postmeta.meta_value as price FROM $wpdb->posts posts, $wpdb->postmeta postmeta WHERE posts.ID = postmeta.post_id AND postmeta.meta_key = 'price' AND posts.post_type = 'product' ORDER BY postmeta.meta_value+0 DESC
Hi,
I have a field that contains 3 characters for instance number then d as in day, h as in hour and m as in minutes eg:-
0d 19h 32m
using your query works in a fashion but as you can see it matches on the 1st “field” (0d) and does not set any order for the remaining fileds
0d 19h 32m
0d 0h 0m
4d 11h 39m
4d 20h 31m
4d 20h 33m
4d 20h 31m
6d 2h 1m
8d 18h 57m
177d 2h 20m
195d 18h 47m
the command used is as detailed below:-
SELECT * FROM hosts WHERE host_status = ‘HOSTDOWN’ AND acked != ‘1’ ORDER BY `hosts`.`host_time`+0 ASC
thanks
Sean
Hardwood home furniture has one thing really all natural concerning it.
There is this sense of coziness, of nature as well as of style that may be
be discovered in wood furniture. Hardwood is born from the planet.