Menu
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`
505
You can use CAST() with MAX() for this. Since the string is filled with string and integer, fir example, “STU201”, therefore we need to use CAST().
Discussion: To find the max value of a column, use the MAX() aggregate function; it takes as its argument the name of the column for which you want to find the maximum value. If you have not specified any other columns in the SELECT clause, the maximum will be calculated for all records in the table.
MAX(x) - 1 simply means the max value of x in the table minus one. You can always use parenthesis and aliases ( as some_cool_name ) to make thing clearer, or to change names in the result. But the first syntax is perfectly valid.
The SQL MIN() and MAX() Functions The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.
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.
88
select ifnull(max(CONVERT(invoice_number, SIGNED INTEGER)), 0)
from invoice_header
where invoice_number REGEXP '^[0-9]+$'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
