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?