Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Findoing next in number in custom sequence

Findoing next in number in custom sequence

From: compboy <compboy80_at_hotmail_spam.com>
Date: Mon, 6 May 2002 16:07:17 -0700
Message-ID: <ude35nckamdoa9@corp.supernews.com>


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 TIA Received on Mon May 06 2002 - 18:07:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US