Show Begin and End of a Series

This came from an IT-Toolbox question. The question was how to find the beginning and ending values of a set of series. Joe Celko answered the question with a non-Oracle version. His answer was so cool, I knew I had to keep it.

What's shown in the screen shot is the result from the subquery. By subtracting the row number from the integer, you can display a group number that will be the same until an integer is skipped. I added 'RowNum As Nbr,' to the subquery just so you could see what was going on. It's totally unnecessary.

Drop Table Foobar;
Create Table Foobar
(I Number(2) );

Insert into Foobar Values (3);
Insert into Foobar Values (4);
Insert into Foobar Values (5);
Insert into Foobar Values (9);
Insert into Foobar Values (10);
Insert into Foobar Values (16);
Insert into Foobar Values (17);
Insert into Foobar Values (18);

Set Echo On
Select Min(I) Begin_Series,
       Max(I) End_Series
  From (
        Select I,
               RowNum     As Nbr,
               I - RowNum As Grp_Nbr
          From Foobar
       )
Group By Grp_Nbr;

BEGIN_SERIES END_SERIES
------------ ----------
         3          5
         9         10
        16         18

3 rows selected.