On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, July 23, 2008

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.

No comments:

Post a Comment