CODEWITHSUNDEEP
phpmysqlmax
N4ta nata
N4ta nata

Reputation: 181

how to query get max id from varchar type and the values in numeric?

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

Upvotes: 5

Views: 3296

Answers (3)

Ben Lee
Ben Lee

Reputation: 53319

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.

Upvotes: 7

Devart
Devart

Reputation: 121922

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

Upvotes: 1

Adam Hopkinson
Adam Hopkinson

Reputation: 28795

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

Upvotes: 3

Related Questions