I have a table in which there is a column containing values to populate a dropdown box. Depending on the DROPDOWN_SUBJECT the DROPDOWN_VALUE may be text or a number, so the column is a varchar(50).

In a certain context, I want to extract the last entry for a particular numeric type

However SELECT MAX(DROPDOWN_VALUE) FROM ...
is fine until the values goes over 10, where it will ALWAYS return 9

I was expecting data type conversion to be automatic, so I tried casting it to integer

SELECT MAX(CAST(DROPDOWN_VALUE AS SIGNED)) FROM ...
fails with syntax error. Same for UNSIGNED. Even in a basic query window CAST("1" AS SIGNED) gives the same error.

It must be obvious, but I am really, really stuck. Any help or pointers would be gratefully received.

Why would it work for numbers below 10 and then get stuck like this after.

example:

SQL-query :

SELECT MAX( CAST( DROPDOWN_VALUE AS SIGNED ) ) AS last_value
FROM `DROPDOWN`
WHERE DROPDOWN_SUBJECT = 'POSTTYPE'

MySQL said:

#1064 - You have an error in your SQL syntax near '( DROPDOWN_VALUE AS SIGNED ) ) AS last_value
FROM `DROPDOWN`
WHERE DROPDOWN_' at line 1


Fr. Simon Rundell
Parish of the Holy Spirit