Finding max in mysql when column is varchar

Question

I have a varchar field that is code of letters and is auto incremented (manualy), so after A is B until after Z becomes AA and so on.

My issue is when I hit AA and I try to select MAX of this field:

SELECT MAX(letter) from jobs

Returns Z instead of AA. This is correct if you are ordering names, but I have a code. There's a way to get this right?

Answer 1

Try this one. This will force it to weight longer strings as being higher than shorter strings:

SELECT letter
FROM jobs
ORDER BY LENGTH(letter) DESC, letter DESC
LIMIT 1;

Edit: Yup, forgot to sort DESC to get the MAX...

Answer 2

This may help? Sort them by length first, then by letter.

SELECT letter FROM jobs
ORDER BY LENGTH(letter) DESC, letter DESC
LIMIT 0, 1