21

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 ?

che
  • 12,097
  • 7
  • 42
  • 71
jmp
  • 2,456
  • 3
  • 30
  • 47

4 Answers4

54

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.

che
  • 12,097
  • 7
  • 42
  • 71
  • 3
    I would not have expected that to work but [it does](http://sqlfiddle.com/#!2/e9c9d/4) – Conrad Frix Jul 30 '12 at 20:04
  • 3
    Interesting, would have thought converting '80.0 proc' to decimal would fail. Anyone know why? – Bort Jul 30 '12 at 20:06
  • 1
    @Bort: Could not find any specification of this [in the docs](http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html), so I'd simply guess they chose to be a bit more lenient instead of returning NULL, which is probably the only way to fail in these functions. – che Jul 30 '12 at 20:21
  • 1
    @Bort: in my testing, it's as if the function reads through the string up to the first "invalid" character, up to the point where the string can no longer be converted to decimal. It does the conversion on the part that can be converted. And fair warning, it considers a "comma" to be an invalid character, `CONVERT('123,456.78',DECIMAL)` will return `123`. – spencer7593 Jul 30 '12 at 21:05
  • Had a similar issue where originally the field to order by was alpha, e.g. Math, Science, Zoology. Then it was decided the categories didn’t work for some of the items, so things were grouped into Levels, e.g. Level 1, Level 2. Then they decided that for one app, they’d just number everything. So in half the database rows the field is a varchar and in half it is really an integer. The conversion I used was ORDER BY CONVERT(`group`, UNSIGNED) WHERE `app` = 'App1' – JScarry Nov 18 '13 at 21:28
  • Thanks for this, I am dealing with some legacy code using a 900 line long method to generate charts and had to convert my int to a varchar for the charts to interprate it correctly, but then anything over 10 values and it would order it incorrectly. Now in the same query i Convert to trick the charts into displaying correctly, then Convert when I ORDER BY to get it to display in order no matter the number. Thanks!! – Josh Sep 27 '17 at 18:23
  • this saves me . thank you – aminbadri7 May 31 '23 at 17:13
9

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;
Ray
  • 40,256
  • 21
  • 101
  • 138
  • nice, but I can't change it, need a solution. – jmp Jul 30 '12 at 20:02
  • Doubles or floats can't hold alphanumeric values like "100.0 proc". – JJJ Jul 30 '12 at 20:06
  • Yes, if at all possible, the column should be changed appropriately (and the fact that `'proc'` appears in every row is a concern). However, depending on what's actually being stored, floating-point types are not the correct choice - I may have recommended either an integer type (if the decimal places did not matter) or a decimal type (if exact accuracy was required). – Clockwork-Muse Jul 30 '12 at 20:09
  • @Ray please check your answer in your server first then publish – Ariful Islam Jul 30 '12 at 20:11
  • 4
    It apparently works, only [needs to be written](http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast) as [`cast(proc as decimal)`](http://sqlfiddle.com/#!2/e9c9d/8) – che Jul 30 '12 at 20:12
1

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.

Community
  • 1
  • 1
Bruno
  • 119,590
  • 31
  • 270
  • 376
1

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;
AntonioHS
  • 1,360
  • 10
  • 25