CODEWITHSUNDEEP
mysqlsql
Mr. Smit
Mr. Smit

Reputation: 2542

How to ORDER BY varchar field as number?

I have problem with sort/order by, not working like I need.

SELECT `proc` FROM `table` ORDER BY `proc` DESC;

Result:

80.0 proc
70.0 proc
60.0 proc
50.0 proc
40.0 proc
200.0 proc
20.0 proc
190.0 proc
180.0 proc
170.0 proc
160.0 proc
150.0 proc
140.0 proc
130.0 proc
120.0 proc
110.0 proc
100.0 proc

What I need is:

200.0 proc
190.0 proc
180.0 proc
170.0 proc
160.0 proc
150.0 proc
140.0 proc
130.0 proc
120.0 proc
110.0 proc
100.0 proc
90.0 proc
80.0 proc
70.0 proc
60.0 proc
50.0 proc
40.0 proc
20.0 proc

How to do it ?

Upvotes: 21

Views: 67084

Answers (4)

AntonioHS
AntonioHS

Reputation: 1370

If you always have the same number of decimal points, my approach would be:

SELECT `proc` FROM `table` ORDER BY LENGTH(proc) DESC, proc DESC;

Upvotes: 1

Bruno
Bruno

Reputation: 122759

There's something fundamentally wrong with your table design. Instead of using values like '80.0 proc' in a VARCHAR column, you should just keep 80.0 in a column of type REAL (or any suitable numerical type that's appropriate for your data). You could do dynamic conversion, only to be used in the ORDER BY expression, but this is also likely to deteriorate the performance of your query.

Adding "proc" to your text here doesn't seem useful, and it will also prevent you from doing a simple conversion.

Surprisingly (see che's answer), apparently, convert(..., decimal) is capable of ignoring the trailing rubbish. It's not something you should rely on in general, though.

The documentation on this aspect of the conversion isn't particularly clear. It's worth reading that section to be aware of the limitations of string/numbers (which would happen in general), for example:

mysql> SELECT '18015376320243459' = 18015376320243459;
        -> 0

If that behaviour changed, you could probably use replace() in this case, just to get rid of ' proc'.

For something more complex, you could potentially use a regular expression replacement to extract the numerical value from your string and cast it into a number before sorting, but this is not supported out of the box in MySQL and that would be rather clunky anyway (fix the problem at its source: your column data type).

To deal with your legacy data, you could add an extra column and use an external program (in any language that's capable of doing a regexp replace), which shouldn't be too difficult.

Upvotes: 1

Ray
Ray

Reputation: 41508

The column field for proc is a VARCHAR or CHAR and it's treating it as a literal string--sorting alphabetically.

Convert the column to double or float or cast the value

SELECT `proc` FROM `table` ORDER BY CAST(`proc` AS decimal) DESC;

Upvotes: 11

che
che

Reputation: 12273

It looks like "proc" is a string (varchar field), so it gets ordered lexically. If it is so, you can probably order it by

SELECT `proc` FROM `table` ORDER BY convert(`proc`, decimal) DESC;

Please note that such queries will be very slow, and for any serious usage it's better to use numeric columns for storing numeric data.

Upvotes: 54

Related Questions