Siva Academy

Youtube.com/SivaAcademy  Oracle SQL  |  PLSQL  |  SQL PLSQL Interview Questions  |  PLSQL Scenario based Interview Questions  |  Subscriber Questions & Answers

  |  SQL practical Question  |  SQL Performance Tuning  |  New Features  |  

  Trigger     |    View  

Saturday, December 14, 2019

query to find the consecutive numbers


-------------------------------------------------------------
Data Setup
create table t(c number);

insert into t values(6);
insert into t values(10);
insert into t values(11);
insert into t values(12);
insert into t values(57);
insert into t values(77);
insert into t values(98);
insert into t values(99);
insert into t values(100);
insert into t values(120);
insert into t values(125);

commit;
-------------------------------------------------------------
Method 1
select c 
from (
    select c, 
           lag(c,1) over(order by c) lag,
           lead(c,1) over(order by c) lead
    from t)
where c=lag+1 or c=lead-1;
-------------------------------------------------------------
Method 2
select c from (
select  c,count(1) over(partition by c-rownum) g
from t ) where g > 1;
-------------------------------------------------------------
Method 3
select c.c
from t c, t prev
where c.c = prev.c +1
union
select c.c
from t c, t next
where c.c = next.c -1;
-------------------------------------------------------------

No comments:

Post a Comment