To get next number in the sequence. 1

[asimasm]

Can any body send me a query for the below problem:
I would like to get a number that is next in the series.
Consider a Table Tbl1 having a integer type column having the following data
1
2
3
4
999

Now i want to get a number that is the next missing number in the series. In the above example i shold get answer = 5 bcz in the series 5 is the next number.
CAn somebody tell me a select statement that gets the above mentioned result.

 

[rajeevnandanmishra]

Hi asimasm,

I am not sure if this code is efficient also.
------
select min(a.intCol)+1 from tbl1 a where a.intCol not in
(select b.intCol from tbl1 b , tbl1 c
where b.intCol < c.intCol and b.intCol+1 >= c.intCol)
------

But may it help you!

 

[tlbroadbent]

rajeevnandanmishra,

Nice solution. I thought it was worth a star.

I'v tested the solution 5000 rows and 25000 rows. It is actually very efficient. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[asimasm]

Hi rajeevnandanmishra
Thanks for the solution. That really solved my problem.

Thanks once again

 

[tlbroadbent]

After experimenting a bit with the solution recommended by rajeevnandanmishra, I have developed a faster solution. I decided to create a FAQ in this forum describing techniques for finding gaps in date or number sequences. Please check out faq183-840 and let me know what you think.

Thanks, Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost