Solved

Oracle SQL Query Count Most Consecutive Numbers

Posted on 2014-01-10
3
2,280 Views
Last Modified: 2014-01-10
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?
0
Comment
Question by:claghorn
  • 3
3 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39771568
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;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39771590
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
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39771873
slightly simpler version...

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

or, if you don't need the id, just whatever has the most consecutive years...

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


note these you can't use row_number, you must use dense_rank
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now