0

I have a simple table with mixed values. For this example lets say i have 3 fields: key, value, and is_number. Key is any key, value is any value and is_number is 1 if value is a number and 0 if it is a string. Value has the type string.

Now i need to sort by values and i try to do the following:

SELECT *
FROM table
ORDER BY IF(is_number, CAST(value AS DECIMAL(20,10)), value)

But even though is_number is 1 for all rows it still sorts by the string value and not the casted integer value.

So i get a order like this 103, 111, 54 instead if the interger values that should give this 54, 103, 111

IF i remove the IF and just keep CAST(value AS DECIMAL(20,10) it works??

Anyone that can see what i do wrong?

user28906
  • 91
  • 1
  • 5
  • 1
    Be careful: That's going to be punishingly slow on large tables. – tadman Jan 17 '17 at 20:42
  • 1
    An expression can only be evaluated as one type or the other, can't have multiple types. – Shadow Jan 17 '17 at 20:44
  • Your casted decimals will be casted back to string by the engine, so it can compare it with the other strings. Try LPAD() insted. – Paul Spiegel Jan 17 '17 at 21:17
  • Try [Natural Sort](http://stackoverflow.com/a/12257917)? Please see this [SQL Fiddle](http://sqlfiddle.com/#!9/fb70e/3) that shows results when only `ORDER BY` is used and also when `ORDER BY` is used in conjunction with Natural Sort. – Dhruv Saxena Jan 17 '17 at 21:30

0 Answers0