CODEWITHSUNDEEP
mysqlmax
Peter Stuart
Peter Stuart

Reputation: 2434

MySQL MAX returns incorrect result

I have a table where the 'text' column (using the text datatype) has numeric values between 0-2000.

When I execute the following MySQL function below, the query results a value of 990 instead of 2000. Could anybody think why this might be?

Thanks in advance:

SELECT max(text) FROM table

Upvotes: 1

Views: 56

Answers (4)

Wei Feng
Wei Feng

Reputation: 1

you have set the column in text type,so they are compared in text type(990 is bigger than 2000).you should compare them in numeric type,you can do it like SELECT max(text*1) FROM table

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

Convert the text field to unsigned int first.

select MAX(cast(`text`as unsigned)) from `table`;

Upvotes: 2

Kyle Hale
Kyle Hale

Reputation: 8120

It's definitely not an "incorrect" result. Per the documentation:

MAX() may take a string argument; in such cases, it returns the maximum string value.

And "990" definitely comes after "2000" when they're strings.

Upvotes: 0

Chris
Chris

Reputation: 2806

You will have to convert the type of text to a number prior to passing it into max().

For example: select max(cast(text as integer)) from table

This may fail if not all the values in text are numeric.

Upvotes: 0

Related Questions