Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to count continuous years

589626Feb 25 2009 — edited Feb 26 2009
I have a table with the following data:

ID YEAR
1 2009
1 2008
1 2007
1 2006
1 2005
1 2004
1 2002
1 2001
2 2009
2 2008
2 2005

I want to be able to count the number of records from the current year back, but if there is a break I want to stop the count, so it is only counting continuous years that are being counted. So for the above data I would want the following returned:

ID COUNT
1 6
2 2

I cannot figure out how to do this using either a cursor or Analytical functions. Any ideas would be greatly appreciated.

Thanks,
Andrew

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 26 2009
Added on Feb 25 2009
7 comments
6,481 views