Advertisement

Find the max value in the last digit

I am using MySQL 5.5. I am facing a problem on how to find the max value in the last digit.

For example below the table, I want to get the max value is detected the last digit. The result should be 100-1-15

Table name: abc
+----+------------+
| id | code       |
+----+------------+    
| 1  |   100-1-1  |
| 2  |   100-1-2  |
| 3  |   100-1-15 |
| 4  |   100-1-6  |
| 5  |   100-1-3  |
| 6  |   100-1-5  |
| 7  |   100-1-9  |
+----+------------+

I am using below the SQL query, but doesn’t work:

SELECT id,max(code) FROM abc;

Hope someone can guide me how to solve it and can get the max code is 100-1-15. Thanks.

Advertisement

Answer

SELECT *
from abc
order by SUBSTRING_INDEX(code, '-', -1) + 0 desc
limit 1

Advertisement