CODEWITHSUNDEEP
mysqldatabase
CD_NS
CD_NS

Reputation: 319

Wrong value Max function in MySQL

I am trying max() and min() function on one column but its giving me wrong result.

The numbers are 1500,500,200,800 and 700.

When I query 

select max(trip_total_catches_kg) 
from trips

it gives me result 800 where it should be 1500 and minimum gives me 1500.

Why is it giving me wrong results?

Upvotes: 1

Views: 1960

Answers (4)

Rick James
Rick James

Reputation: 142258

The most terse answer:

select max(0 + trip_total_catches_kg) from trips;

Upvotes: 1

void
void

Reputation: 7880

perhaps the type of values are varchar, convert them to integer then get max or min of them:

SELECT MAX(CONVERT(trip_total_catches_kg,UNSIGNED INTEGER))
FROM trips;

Upvotes: 1

MrSimpleMind
MrSimpleMind

Reputation: 8587

as @marc_s commented... It is because of the column type, which is char / varchar and not integer.

here is a sqlfiddle as a demo. http://sqlfiddle.com/#!2/0ab63/1

Convert your column to a integer instead.

alter table trips modify trip_total_catches_kg int;

Upvotes: 1

messanjah
messanjah

Reputation: 9278

As @marc_s mentioned in a comment, I would double-check that the type of the trip_total_catches_kg column is numeric (integer), instead of string (varchar(255)).

The behavior you describe would make sense if the column was a string type, as @marc_s said:

800 Is the "max" - since the number 8 at the first position is the largest of those characters

Upvotes: 2

Related Questions