Finding next number in custom sequence

I have a table that has information in it that represents a message:

DESTINATION VCHAR(20)

SEQ_NUM         NUMBER(4,0)
PAYLOAD         LONG RAW

*note each destination has it own SEQ_NUM sequence.

I have a program that uses the table to store messages while it is trying to send them. When my server is shutdown for some reason I need to be able to find the next sequence number available so that I can start the message numbering all over again when the program starts up again. The sequence number is from 1-2047. The problem I am having is when the following situation occurs with the sequence numbers: DESTINATION SEQ_NUM

Joe                         2045
Joe                         2046
Joe                         2047
Joe                         1
Joe                         2
Joe                         3
...
Mark                      100
Mark                      101
Mark                      102

How can I write my select statement to find that the next number for Joe is 4. When there is no loop through, I was just finding the largest SEQ_NUM with MAX(SEQ_NUM) and adding 1 to it.
SELECT DESTINATION,MAX(SEQ_NUM),MIN(SEQ_NUM) FROM IASPROCESSING GROUP BY DESTINATION I am using Oracle 8.1.6i

I didn't want to use an Oracle sequence because I have 4000+ different destinations and I didn't want to create that many sequences.

TIA Received on Mon May 06 2002 - 18:15:29 CDT