I need to get maximum number from a part of the value that generally start with year followed by slash(
SELECT columname FROM tablename WHERE columname LIKE '2016/%' ORDER BY id DESC
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'