Home » SQL & PL/SQL » SQL & PL/SQL » How do I find a gap in sequence?
How do I find a gap in sequence? [message #213809] Fri, 12 January 2007 06:55 Go to next message
Buchas
Messages: 83
Registered: March 2006
Member
Hello,

How do I find a minimal gap in sequence?

E.g. I run this test script:

create table tmp_t as
select 100 as numerrr from dual;

insert into tmp_t values (1);
insert into tmp_t values (2);
insert into tmp_t values (3);
insert into tmp_t values (7);
insert into tmp_t values (8);
insert into tmp_t values (10);

Now If i do

select * from app_user.tmp_t;

I see

NUMERRR
100
1
2
3
7
8
10

I want to write a SQL query, that would return number 4- this is a first gap in sequece 1,2,3,.,.,.,7,8,.,10,

How do I do that?

Help please Wink

[Updated on: Fri, 12 January 2007 07:00]

Report message to a moderator

Re: How do I find a gap in sequence? [message #213812 is a reply to message #213809] Fri, 12 January 2007 07:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This might be one way to do that:
SELECT MIN (lg) + 1
  FROM (SELECT   numerr, LAG (numerr) OVER (ORDER BY numerr) lg
            FROM tmp_t
        ORDER BY 1)
 WHERE numerr - lg > 1
Re: How do I find a gap in sequence? [message #213814 is a reply to message #213812] Fri, 12 January 2007 07:28 Go to previous message
Buchas
Messages: 83
Registered: March 2006
Member
wow.
thanks, it works!
Previous Topic: About Trigger
Next Topic: Oracle Trigger trim problem
Goto Forum:
  


Current Time: Thu Dec 08 20:30:25 CST 2016

Total time taken to generate the page: 0.18352 seconds