Ordering varchar field with decimals numbers and none decimal numbers

Question

Varchar field with decimals numbers and none decimal numbers.

Ex:

100.02
99
9
200.56
500.4
10.1
6

How do i order it to display in ASC order

I tried below codes with no luck

SELECT * FROM posts ORDER BY CAST(price AS DECIMAL(10,2)) DESC LIMIT 0, 9

Also

SELECT * FROM posts ORDER BY convert(price, decimal) DESC LIMIT 0, 9

But none of the above codes seems to work. Can someone point me out the proper way to do this.

Answer 1

The sorting issue is due to the use of a sting type field for a number, it will never sort as a human would expect.

if the field type is price, you should never use float, instead you should use DECIMAL or NUMERIC

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data.

you don't want an "approximate" value for a price

The FLOAT and DOUBLE types represent approximate numeric data value