how to sort varchar numeric columns by DESC or ASC?

mysql sql-order-by natural-sort
Henrikh picture Henrikh · Oct 16, 2011 · Viewed 16.2k times · Source

I write ... 

ORDER BY column ASC

but my column is VARCHAR and it sorts wrong like 1, 10, 2, instead of 1, 2, 10.

How can I do it to sort like 1, 2, 10?

Answer

gbn picture gbn · Oct 16, 2011 
order by 
   cast(column as float)

Notes:

  • Assumed you only have numbers in the columns. No "fish" or "bicycle"
  • empty strings CAST to zero

Edit: For MySQL. You can not cast to float

order by 
   cast(column as decimal(38,10))

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL order by before group by

There are plenty of similar questions to be found on here but I don't think that any answer the question adequately. I'll continue from the current most popular question and use their example if that's alright. The task in this …

Read More
Order a MySQL table by two columns

How do I sort a MySQL table by two columns? What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article …

Read More