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 ?

Improve this question
5
  • 6
    Sounds like you're using varchar instead of decimal. Commented Jul 30, 2012 at 19:59
  • 3
    Is the actual text 80.0 proc in the column? Commented Jul 30, 2012 at 20:00
  • yes varchar, yes column contain text: 80.0 proc, etc. Commented Jul 30, 2012 at 20:01
  • Why do they all contain the text proc? Even the column is named proc? I dont get it Commented Jul 30, 2012 at 20:04
  • possible duplicate of Natural Sort in MySQL - if that doesn't work, look at all the questions linked to it. Commented Jul 30, 2012 at 20:05

4 Answers 4

Reset to default
55

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I would not have expected that to work but it does
Interesting, would have thought converting '80.0 proc' to decimal would fail. Anyone know why?
@Bort: Could not find any specification of this in the docs, 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.
@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.
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'
|
11

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;
Improve this answer

5 Comments

nice, but I can't change it, need a solution.
Doubles or floats can't hold alphanumeric values like "100.0 proc".
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).
@Ray please check your answer in your server first then publish
It apparently works, only needs to be written as cast(proc as decimal)
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.

Improve this answer

Comments

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;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.