Developer Developer - 1 year ago 153
SQL Question

mysql get max number from a string field

I need to get maximum number from a part of the value that generally start with year followed by slash(

). So I need a maximum number after the slash(/) but year should be 2016


I tried this query

SELECT columname FROM tablename WHERE columname LIKE '2016/%' ORDER BY id DESC

the above query always giving '2016/469' as first record, how to get '2016/0470' as the maximum number?

any help will be much appreciated.

Thank you.

Answer Source

You need to split the string into 2 parts and evaluate them as numbers, instead of strings. The following formula will return the number after the / in the fieldname. All functions used below are described in the string functions section of the MySQL documentation. This way you can get the number after the / character, even if it is not year before the /, but sg else. The + 0 converts the string to a number, eliminating any leading 0.

select right(columnname, char_length(columnname)-locate('/',columnname)) + 0
from tablename

Just take the max() of the above expression to get the expected results.


If you need the original number and the result has to be restricted to a specific year, then you need to join back the results to the original table:

select columnname
from tablename t1
inner join (select max(right(t.columnname, char_length(t.columnname)-locate('/',t.columnname)) + 0) as max_num
            from tablename t
            where left(t.columnname,4)='2016'
           ) t2
           on right(t1.columnname, char_length(1t.columnname)-locate('/',t1.columnname)) + 0 = t2.max_num
where left(t1.columnname,4)='2016'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download