Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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
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,237 views