CODEWITHSUNDEEP
mysql
Tom
Tom

Reputation: 235

MySQL, Max doesnt appear to be working?

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???

Upvotes: 2

Views: 683

Answers (2)

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171559

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.

Upvotes: 3

182764125216
182764125216

Reputation: 956

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

either:

cast(variable as int)

or

convert(int, variable)

Upvotes: 1

Related Questions