Menu
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 ??
377
To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
MAX is an aggregate function that evaluates the maximum of an expression over a set of rows (see Aggregates (set functions)). MAX is allowed only on expressions that evaluate to built-in data types (including CHAR, VARCHAR, DATE, TIME, CHAR FOR BIT DATA, etc.).
The MAX() function returns the largest value of the selected column.
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.
92
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
