Clever Geek Handbook

Find max in mysql when column is varchar

I have a varchar column that has data like this

top<somenumber>

therefore, the word "top" follows the numbers. I am trying to insert a record into this table and I am trying to figure out what is the next value to come in.

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

maybe something like this?

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

how can I form the above query to account for only numbers followed by 'top'

+2


source to share


2 answers


Well, in general, you can use max

:

select
    max(catalogid)
from
    catalog

However, here we have a small problem when we need to remove top

:



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

You see, here we are using substring

to limit the field to catalogid

just a number.

Alternatively, you can check out aggregate functions , as they can be quite useful!

+3


source


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



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


source






More articles:


All Articles