Avatar of claghorn
claghorn

asked on 

Oracle SQL Query Count Most Consecutive Numbers

CREATE TABLE EX2
   (      ID NUMBER,
      YEAR NUMBER
   );

Insert into EX2 (ID,YEAR) values (1,2010);
Insert into EX2 (ID,YEAR) values (1,2000);
Insert into EX2 (ID,YEAR) values (1,2009);
Insert into EX2 (ID,YEAR) values (1,2011);
Insert into EX2 (ID,YEAR) values (1,2012);
Insert into EX2 (ID,YEAR) values (1,2013);
Insert into EX2 (ID,YEAR) values (1,2001);
Insert into EX2 (ID,YEAR) values (1,2002);
Insert into EX2 (ID,YEAR) values (1,2003);
Insert into EX2 (ID,YEAR) values (1,2005);
Insert into EX2 (ID,YEAR) values (1,2006);

This query works fine:

select
count(*) Keep(Dense_Rank Last order by distance) as cnt
from (select id,
      Year+Row_Number() over(partition by id order by YEAR desc) as distance
        from ex2)
group by ID;

It returns 5 which is correct but if you insert a duplicate ie:
Insert into EX2 (ID,YEAR) values (1,2011);
it will not count consequtive years properly anymore.

Can you make it get over this duplicate problem and still return the 5 with that duplicate in there?
Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon