Re: CONSECUTIVE Values

robert,

your post is a little unclear (or maybe my reading of it is)... if there are gaps in the column values, the literal answer to your questions is you can't ensure returning a set of consecutive values, because by definition you don't have one

however, if you're asking how to determine if there are gaps in the sequence then you can do something like this

select t1.c_year
from t t1, t t2
where t1.c_year + 1 = t2.c_year(+)
and t2.c_year is null
and t1.c_year <> (select max(c_year) from t)

this is basically an outer self join that tries to match each record with one for the following year -- outer join includes years that do not have a matching 'next' year, and the IS NULL predicate filters the output to just those years
the subquery eliminates the max year, as it will never have a 'next' year

this will return a list of years that precede a gap, but won't identify how big the gap is

--mcs

"Robert C" <rchin_at_panix.com> wrote in message news:bpdvhc$2fu$1_at_reader2.panix.com...
| SQL> select MIN(c_year), MAX(c_year)
| 2 from T
| 3 ;
|
| MIN(C_YEAR) MAX(C_YEAR)
| --------------------------- ---------------------------------------------

--


| -

| 1996                                  2005

|

| There may be gaps in this column value...

| How can I ensure to return a result of CONSECUTIVE values...like:

|

| 1996

| 1997

| 1998

| 1999

| 2000

| 2001

| 2002

| 2003

| 2004

| 2005

|

| Thanks

|

|