how to select max of mixed string/int column?

mysql select max mixed
CairoCoder picture CairoCoder · May 11, 2013 · Viewed 46.1k times · Source

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

nakosspy picture nakosspy · May 11, 2013

HKL9 (string) is greater than HKL15, because 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.

Related questions

MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
Create a temporary table in a SELECT statement without a separate CREATE TABLE

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I …

Read More