CODEWITHSUNDEEP
mysql
Mark Jacobs
Mark Jacobs

Reputation: 23

mysql min value column of floating numbers

Is it possible to find the min value of a column of floating numbers using a mysql function? Suppose I have the following table:

id | value
a | 24.88
a | 119.99

If I try:

SELECT MIN(value) FROM [table name] GROUP BY id;

mysql returns:
119.99

After testing this with different floating numbers I believe that this is the case because mysql takes the first character in each of the strings "1" and "2" and then selects a min based on which character is smaller.

I've read through this forum and others trying to find an answer but it seems nobody has raised this problem.

I should mention I've also tried CEIL(value) but that function also seems to have some bugs and I'd prefer to keep the number a floating number and not an integer.

Thanks everyone.

Upvotes: 2

Views: 2907

Answers (1)

Chris Trahey
Chris Trahey

Reputation: 18290

It looks like the column is being stored as a character-based data type. You can solve this in one of two ways:

  1. Change the column type to a numeric type
  2. change the query to add CAST around the value: MIN(CAST(value AS DECIMAL))

The column change might look like this: 

ALTER TABLE my_table MODIFY COLUMN value double;

And, as far as I know, MySQL will attempt to convert the data for you. See the note here, which states it "tries".

Upvotes: 2

Related Questions