CODEWITHSUNDEEP
mysqlsql
Nakrule
Nakrule

Reputation: 609

Select MAX in mixed int/string in SQL database

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;

Upvotes: 0

Views: 483

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Just use substring_index() and order by:

select t.*
from t
order by substring_index(rd, ':', -1) + 0 desc
limit 1;

Upvotes: 0

slaakso
slaakso

Reputation: 9080

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

Upvotes: 1

Cid
Cid

Reputation: 15247

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 |

View on DB Fiddle

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

Upvotes: 3

Related Questions