how to query get max id from varchar type and the

问题:

i have table and column id, and the values are 1, 2 ,3,...,10,11,12,13. how to query get max id from varchar type ? i had try

select MAX(id) from table

but the result is 9, Please help ??

回答1:

Looks like the values are strings and it selecting the maximum string. You have to cast them to numbers first if you want them to sort numerically. You can use CONVERT to do this:

SELECT MAX(CONVERT(id, SIGNED)) FROM table

You can also use CAST:

SELECT MAX(CAST(id AS SIGNED)) FROM table

They do nearly the same thing except CONVERT has some additional options if you need them.

回答2:

You can cast the varchar to an integer - something like

SELECT MAX(CONVERT(id, SIGNED)) FROM table

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_convert

回答3:

SELECT MAX(id+0) FROM table will do the trick