Developer - 8 months ago 28

SQL Question

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

`/`

`2016/422`

2016/423

2016/469

2016/0470

2014/777

2015/123

2015/989

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

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.

UPDATE:

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'
```

Source (Stackoverflow)