MySQL return new sequential order number in format MMDDYY-#

Question

I've got the following query to generate new order numbers in the format MMDDYY-# where the number is the next in the sequence for today's orders.

select concat(date_format(now(),'%m%d%y'),'-',ifNULL(max(right(po_number, LENGTH(po_number)-7)),0)+1) newPO 
from orders where left(po_number, 6) = date_format(now(),'%m%d%y')

This works fine for orders 1-9 but as I get to 10 I'm going to keep grabbing the 9th as the max and will never be able to generate MMDDYY-11 as an order number. I need to rethink this query but am stuck on the most efficient way to go about it. Anyone have an elegant soltution? Here's a sample table structure for testing.

CREATE TABLE orders (
po_number VARCHAR( 10 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO orders (po_number)
VALUES ('093009-9'), ('093009-10');

Thanks!

Answer 1

your expression splits off the number at the end, then calculates max() based on the fact that it's text. if you want to sort based on the numeric value, you have to cast() it to a number first.