CairoCoder CairoCoder - 6 months ago 86
MySQL Question

how to select max of mixed string/int column?

Lets say that I have a table which contains a column for invoice number, the data type is VARCHAR with mixed string/int values like:

invoice_number
**************
HKL1
HKL2
HKL3
.....
HKL12
HKL13
HKL14
HKL15


I tried to select max of it, but it returns with "HKL9", not the highest value "HKL15".

SELECT MAX( invoice_number )
FROM `invoice_header`

Answer

HKL9 (string) is greater than HKL15, beacause they are compared as strings. One way to deal with your problem is to define a column function that returns only the numeric part of the invoice number.

If all your invoice numbers start with HKL, then you can use:

SELECT MAX(CAST(SUBSTRING(invoice_number, 4, length(invoice_number)-3) AS UNSIGNED)) FROM table

It takes the invoice_number excluding the 3 first characters, converts to int, and selects max from it.

Comments