This is just a quick and simple example on how to convert a varchar column to integer in SQL sorting (order by).
For example, I have an meta key named “post_views_count” in WordPress postmeta table and its value is the total post view for each my article on http://4rapiddev.com (meta_value) and will be counted on every page load for each article.
When I run SQL command via phpmyadmin to get the highest post view list (order by meta_value desc); it returns a list which aren’t my expectation because the meta_value’s data type is varchar; meaning posts have total post view start from 9 will be on the top.
Before convert varchar column to int
1 2 3 4 | SELECT * FROM `4rd_postmeta` WHERE meta_key = 'post_views_count' ORDER BY meta_value DESC |
Convert varchar column to int in MySQL
To fix this issue, I have to convert value of the meta_value column to int before sorting.
1 2 3 4 | SELECT * FROM `4rd_postmeta` WHERE meta_key = 'post_views_count' ORDER BY CAST(meta_value AS UNSIGNED) DESC |
It looks correct now.