Getting MAX when using REGEX match on varchar

[firepages]

I should not be using varchar here I know but I am stuck with it for now..

I have a varchar which mostly stores a serial number [0-9]{1-9} , but sometimes stores an alphanumeric flag 'R' or 'S' etc , I want to be able to suggest the next available serial number , which is basically MAX(um_serial) + 1 , but since its not an integer field max does not work , I currently have ..

SELECT um_serial
FROM user_machines
WHERE um_serial
REGEXP "^[0-9]{2,}" /*using 2 as the minimum as '1-9 are long gone and it avoids catching '0'*/

this does return only the numeric fields , but short of a temporary table , or fetching all of the results and sorting them in PHP , can I/how can I, now select the 'max' value in the same query ?

[raf]

did you try to simply sort them? like

SELECT um_serial
FROM user_machines
WHERE um_serial
REGEXP "^[0-9]{2,}"
ORDER BY um_serial DESC
LIMIT 1

[firepages]

Hi Raf , yes I did but to no avail , sort DESC gives
e.g.

1132
1133
1134
901
902
/*etc*/
1299
1300
956
957

.. so basically in the order they were added/edited!

there are loads of gaps in numbers as you can see which is why I an trying to suggest the next natural number from now on , but a sort seems to be ignored , I assume because its a varchar field? , I think I might run an update and set everything to
0000901
0000902 .... etc , though I dont think even that will help the sorting here without a natsort() type function which I do not think MySQL has.

[raf]

what about

SELECT um_serial
FROM user_machines
WHERE um_serial
REGEXP "^[0-9]{4}"
ORDER BY um_serial DESC
LIMIT 1

<edit>
anyway still a hack ut it should work until you get a 9999. Then you'd need to change it to {5}
of yourse, the only real soution is probably setting up a seperate table witk an autonum field as PK that you then include in thistable as foreign key, or something similar</edit>

[firepages]

Cheers Raf , didn't think about having a seperate table to keep the next number in , thats probably going to be the easiest , I just ran through the code to see how much work it would be to simply change the table to an INT and hold status flags elsewhere and its do-able but not much fun.

So cheers again !