If you order a string column that contains only numeric values with MySQL, it will order them as string values, e.g.: 1, 10, 100, 2, 3, 31, 32, 4, etc. If you want to order them as if they were an integer, cast them in the order by clause.
tl;dr
SELECT * FROM <table> ORDER BY CAST(<column> AS unsigned)
Why?
Normally you’d store numeric values in a numeric column in the database, but sometimes this isn’t possible. For example, the postmeta table in WordPress has a meta_value column which is a string and in which you might store only numeric values for a particular meta_key field.
When the values are strings, they are sorted as strings, so 1, 2, 3, 4, 10, 31, 32, 100 would be sorted as 1, 10, 100, 2, 3, 31, 32, 4 as shown in the example in the first paragraph.
Casting the value as "unsigned" or "signed" in the sort part of the query will instead sort the value as an integer and give the desired result, assuming all the values are indeed numeric.
Speed impact?
I haven’t measure this myself, but there will be an obvious impact in speed, especially if the resultset is large. However, it may be fairly minimal and depending on what you are using this query for, it may not matter.
A couple of WordPress examples
Using WordPress as an example, here’s a query on the wp_postmeta table finding records with a meta_key of _ct_text_552c8612842c1, which was created using CustomPress, and then ordering them numerically:
SELECT * FROM wp_postmeta
WHERE pm.meta_key = '_ct_text_552c8612842c1'
ORDER BY CAST(pm.meta_value as unsigned)
In my case, I needed to join this to all posts of type ‘forum’, and here’s another example:
SELECT p.ID, pm.meta_value FROM wp_posts p INNER JOIN wp_postmeta pm ON pm.post_id = p.ID WHERE p.post_type = 'forum' AND p.post_status = 'publish' AND pm.meta_key = '_ct_text_552c8612842c1' AND pm.meta_value != '' ORDER BY CAST(pm.meta_value as unsigned)
That’s all for now, I hope you found this useful!
