finding max in mysql when column is varchar

Question

I have a varchar column that has data like this

top<somenumber>

so the word 'top' followed by numbers. I am trying insert a record in this table and am trying to find out what is the next value that should go in.

Is there anyway I can find the max value for this column?

maybe something like this?

select * from catalog ORDER BY CAST(`catalogid` AS DECIMAL(10,2)) DESC limit1;

how can i mold the above query to account for only numbers followed by 'top'

Answer 1

If it's always "top[N]", and you want max N, you could try:

SELECT MAX(CAST(SUBSTR(`catalogid`,4) AS DECIMAL(10,2)) ...

Answer 2

Well, generically, you can use max:

select
    max(catalogid)
from
    catalog

However, here we have a fun little issue where we need to remove top:

select
    max(cast(substring(catalogid, 4) as decimal(10,2))) as maxid
from
    catalog

You see, here, we're using substring to limit the catalogid field to be just the number.

Also, you may want to check out aggregate functions, as they can be quite handy!