Solved

Oracle SQL Query Count Most Consecutive Numbers

Posted on 2014-01-10
3
2,252 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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 …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

709 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

13 Experts available now in Live!

Get 1:1 Help Now