Link to home
Create AccountLog in
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?
Avatar of Sean Stuber
Sean Stuber

distinct first?

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 (select distinct id,year from ex2))
group by ID;
no, that doesn't work either

try this..

insert into ex2
select 1, 2000 - level from dual connect by level < 10;

then run either of the above queries.  Even though there is a 13-year consecutive run, neither of the above queries can find it.


Try this...

  SELECT id, MAX(cnt)
    FROM (  SELECT id, COUNT(*) cnt
              FROM (SELECT id, year - DENSE_RANK() OVER(PARTITION BY id ORDER BY year) n
                      FROM (SELECT DISTINCT id, year FROM ex2))
          GROUP BY id, n)
GROUP BY id

you could also use row_number instead of dense_rank
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account