时间戳

首页 > 解决方案 > Select MAX in mixed int/string in SQL database

mysql - 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;

标签： mysqlsql

解决方案

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

推荐阅读