Sachin R
Sachin R

Reputation: 11876

SQL query for sorting numeric fields

I have one column of the type VARCHAR which stores numbers like 10, 11, 16.5, 24, 43, 12, 100, etc.

I want to order these fields but it sorts like this:

10
11
12
16.5
100
24
43

And I'd like this result:

10
11
12
16.5
24
43
100

How can I do this?

Upvotes: 0

Views: 142

Answers (4)

Amirouche Douda
Amirouche Douda

Reputation: 1564

Just add zero to your field without any cast or convert:

order by 0+yourfield ASC

Upvotes: 1

Jordan Running
Jordan Running

Reputation: 106017

VARCHAR is a string type, so it's ordering them alphabetically, which is the expected behavior. If possible you should change the column to a number type. If that's not possible you can cast the value to a number like so:

SELECT ... ORDER BY CAST(column_name AS DECIMAL)

However, this will impact your performance if you have a lot of rows in your database.

Upvotes: 6

Rich Adams
Rich Adams

Reputation: 26574

You want to cast the field to be a numeric value instead, so that MySQL will order it with a numeric comparison, rather than a string comparison.

SELECT your_column 
FROM your_table 
ORDER BY CAST(your_column AS DECIMAL) ASC

The above will work with negative numbers too, if you have any of those in your table.

Although if the field only contains numeric data, you really should be storing it as such, rather than as a varchar. Having a cast in your query will dramatically affect performance as you get more rows.

Upvotes: 1

Randy
Randy

Reputation: 16677

order by to_number(mycol)

this will of course give an error if one value is not numeric. which begs the question - why not make it a numeric column in the first place.

Upvotes: 1

Related Questions