Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


next value

Posted on 2003-02-26
Medium Priority
Last Modified: 2013-12-12
If i have a column in a table with numeric values in it (e.g. projectnr 1|2|3|5|10). How can i get the next value in the table. I want to fill up the holes so that the next time i create a new project, the projectnr will automatically set to 4.  And the time after that, is has to be a 6 etc.
I tried it with a cursor for the numbers in the table and a loop with i 1..999 but that doesn't work.
Any ideas??
Question by:sneyers
LVL 15

Accepted Solution

andrewst earned 200 total points
ID: 8027003
This query will do it:

select NVL(MIN(projectnr),0)+1 FROM
select t1.projectnr
from   t t1
where  not exists( select 1 from t t2 where
t2.projectnr = t1.projectnr+1 )

(Where t = your table)

Author Comment

ID: 8032018
this was very helpful!!

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question