Select Max from table issue

Question

i have a set of data like following:

Id           Name
==           ======
1            name1
10           name10
2            name2
3            name3
4            name4
5            name5
6            name6
7            name7
8            name8
9            name9

If i write select max(id) from table, it suppose return me 10 as the maximum value right? Why I always get the result 9 instead of 10? what is going wrong?

Answer 1

My guess, your Id column is not numeric but a varchar, so it is sorting alphabetically, not numerically.

Answer 2

Presumably Id is a string rather than numeric datatype.

You should change it to a more appropriate datatype such as integer.

If this is not possible then you would need to do a cast to a numeric datatype. e.g. MAX(CAST(Id AS UNSIGNED)) but I suggest fixing it.