22

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`
CairoCoder
  • 3,091
  • 11
  • 46
  • 68

7 Answers7

29

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.

Aan
  • 12,247
  • 36
  • 89
  • 150
nakosspy
  • 3,904
  • 1
  • 26
  • 31
7
select ifnull(max(CONVERT(invoice_number, SIGNED INTEGER)), 0)
from invoice_header 
where invoice_number REGEXP '^[0-9]+$'
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
Sandeep
  • 79
  • 1
  • 1
  • This is the only correct answer here because it works even if the invoice numbers have varying formats and it works without modifying the database. – Vincent Oct 28 '16 at 18:16
  • This doesn't extract numeric part of invoice_number in case there are leading letters – DarkSide May 18 '21 at 16:41
5

After a while of searching I found the easiest solution to this.

select MAX(CAST(REPLACE(REPLACE(invoice_number , 'HKL', ''), '', '') as int)) from invoice_header
Ketan Dubey
  • 430
  • 8
  • 19
2

This should work also

SELECT invoice_number
FROM invoice_header
ORDER BY LENGTH( invoice_number) DESC,invoice_number DESC 
LIMIT 0,1
irfandar
  • 1,690
  • 1
  • 23
  • 24
1

Your problem is more one of definition & design.

Select the invoice number with highest ID or DATE, or -- if those really don't correlate with "highest invoice number" -- define an additional column, which does correlate with invoice-number and is simple enough for the poor database to understand.

select INVOICE_NUMBER 
from INVOICE_HEADER
order by ID desc limit 1;

It's not that the database isn't smart enough.. it's that you're asking it the wrong question.

Thomas W
  • 13,940
  • 4
  • 58
  • 76
1

Below query can be used:

select max(cast((CASE WHEN max_no NOT LIKE '%[^0-9]%' THEN max_no END) as int)) AS max_int_no from table1

Ambreen Haleem
  • 26
  • 2
  • When answering an old question, your answer would be much more useful to other StackOverflow users if you included some context to explain how your answer helps. See: [How do I write a good answer](https://stackoverflow.com/help/how-to-answer). – David Buck Jan 25 '20 at 10:38
0

For mysql database i use below :

SELECT CONCAT('HK','L',MAX(ABS(SUBSTRING_INDEX(invoice_number,'L', -1))))AS maxInvoice FROM invoice_header

because all invoice number can be use letter "L" for an delimiter, use -> SUBSTRING_INDEX() function to split invoice number ,then use concat() function to concatenate them again for get the max invoice number.

Chauncey Lee
  • 85
  • 8