I have the following database:
+-------------+
| rd |
+-------------+
| 65000807:11 |
| 65000807:9 |
| 65000809:10 |
+-------------+
I would like to select the biggest value, which is 65000807:11 (the biggest number after the :). With the following query, I can get 11, but I need to get the whole string. How could I do that?
SELECT MAX(CAST(SUBSTRING(rd,10,length(rd)-9) AS UNSIGNED)) AS 'rd' from myTable;
You can use your substring in an order by clause and get 1 result :
Schema (MySQL v8.0)
CREATE TABLE myTable (
`rd` VARCHAR(11)
);
INSERT INTO myTable
(`rd`)
VALUES
('65000807:11'),
('65000807:9'),
('65000809:10');
Query #1
SELECT rd
FROM myTable
ORDER BY CAST(SUBSTRING(rd,10,length(rd)-9) AS UNSIGNED) DESC
LIMIT 1;
Output :
| rd |
| ----------- |
| 65000807:11 |
However, I would advice you to re-think the design of the table, you are storing 2 informations in the same column, which goes against the purpose of using a RDBMS
If the first number can be variable length, you better use locate:
select cast(substring(rd, locate(':', rd)+1) as signed)
from thetable
order by 1 desc
limit 1
Just use substring_index() and order by:
select t.*
from t
order by substring_index(rd, ':', -1) + 0 desc
limit 1;
