Sort longtext as int in SQL

2 Views

I have table in MySQL database where one column type is longtext and there are stored numbers. I need to get content from table sorted by numbers in that column. 

SELECT * FROM wp_postmeta WHERE meta_key = 'rating_avg' ORDER BY meta_value

With this query sorting is not proper and looks like:

Read More
0
1.6
10
5

but I need like this: 

10
5
1.6
0

I may not change column type, because this column have many different types of data. Is there any possibility to change column type temporary in SQL query?

Post Views: 2

Related posts

Leave a Reply

3 comments

  1. What you are looking for is CAST.

    CAST(expr AS type)

    Your SQL Query should look like this:

    SELECT * FROM wp_postmeta WHERE meta_key = 'rating_avg' ORDER BY CAST(`meta_value` AS DECIMAL) DESC

  3. It’s been over a year since the question was asked, but for reference to anyone that came here after the search, the answer is

    CAST(meta_value AS DECIMAL(10,2))

    so the query should be:

    SELECT * FROM wp_postmeta WHERE meta_key = 'rating_avg' ORDER BY CAST(`meta_value` AS DECIMAL(10,2)) DESC