MySQL, Max doesnt appear to be working??

Question

I have a table containing data and one of the fields is 'class'

When i do:

select* from cvs where `Player ID` = <a_number> 

i get data containing class numbers from 1 to 10

If i then do

select max(`Class`) from cvs where `Player ID` = <a_number>

it returns 9 and not 10?

I deleted the 10th class data and it still returned 9, i deleted the 9th class data and it returned 8

Class is a varchar(50)

What is happening???

Answer 1

If your Class field is only populated with integers, try:

select max(cast(Class as UNSIGNED)) 
from cvs 
where `Player ID` = <a_number> 

MySQL is doing an alphanumeric sort rather than numeric, since it is a varchar. When it compares the first characters, '9' is greater than '1', so that gets sorted after '10'.

Ideally, if the data is numeric, then it should be stored using the appropriate numeric type, rather than varchar. This will also avoid problems where a query that works fine now stops working in the future just because a single non-integer value was inserted in the Class column.

Answer 2

its not an int so its alphabetizing them. and 9>1.

either:

cast(variable as int)

or

convert(int, variable)