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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

764 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