MySQL : select max(id) when id is a varchar

Be very careful when trying to retrieve an id composed of digits but stored as a varchar in your MySQL database!
For instance:
  • In table account, the id is made of a reference number (4 digits), a slash synbol (/) and a copy number (made of digits: how many time this account has been copied).
  • I have 11 copies of account number 3651:
    • Account 3651/0 (the initial one)
    • Account 3651/1
    • Account 3651/9
    • Account 3651/10
The biggest id for my 3651 account should be the last one: 3651/10, but

SELECT max(id) FROM account where id like ‘3651’

will answer 3651/9 because ‘9’ is greater than ’10’ “varchar speaking”.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: