Grouping sequential numbers with SQL

[Finomosec]

Hi,

i have a table of number-objects with beginning and endnr:
10-15
16-20
25-30
32-32
35-35
36-36
37-40

And what i need is the min(beginning) and max(endnr) of each group containing sequential objects with PREV.endnr+1=NEXT.beginning:
10-20
25-30
32-32
35-40

I found SQL Query - Find block of sequential numbers, but it seems to be just the negation of what i need in a way that is not reversed so simply (or i did not really understand what happened there).

I thought about using hierarchical queries, but could not it out.

A workaround to the problem would be writing a PL/SQL-Function, but i dislike that. Pure SQL would be better.

Greetings Finomosec;

[Finomosec]

Here is my PL/SQL-solution for the problem.
But i would still like to see your SQL-solutions (if any).

CREATE OR REPLACE TYPE beg_end AS OBJECT (beginning VARCHAR2(15), endnr varchar(15));
/
 
CREATE OR REPLACE TYPE beg_end_set IS TABLE OF beg_end;
/
 
CREATE OR REPLACE FUNCTION groupNumbers RETURN beg_end_set PIPELINED IS
    CURSOR pns IS SELECT * FROM numbers ORDER BY beginning ASC;
    res beg_end := beg_end(null, null);
    curPN numbers%ROWTYPE;
    beginning VARCHAR2(15) := null;
    endnr VARCHAR2(15) := null;
BEGIN
    OPEN pns;
    LOOP
        FETCH pns INTO curPN;
        EXIT WHEN pns%NOTFOUND;
        IF beginning IS NULL THEN
            beginning := curPN.beginning;
        ELSIF to_number(endnr) + 1 != to_number(curPN.beginning) THEN
            res.beginning := beginning;
            res.endnr := endnr;
            PIPE row(res);
            beginning := curPN.beginning;
        END IF;
        endnr := curPN.beginning;
    END LOOP;
    IF beginning IS NOT NULL THEN
        res.beginning := beginning;
        res.endnr := endnr;
        PIPE row(res);
    END IF;
    CLOSE pns;
    return;
END;
/
SHOW ERRORS
 
 
-- then it can be used like this ...
select count(*) from table(groupNumbers);
 
-- count for diff ...
select count(*) from numbers;

[Finomosec]

I tried to figure out a way to solve the problem using Oracle's analytic functions, but did not find a way to solve the problem so far.

I need a funtion to group rows together by a where-clause.
Something like this:

SELECT min(beginning), max(endnr)
FROM numbers n
group by where (prev.endnr +1 = n.beginning);
 

Maybe its just another syntax to use ...

Any ideas?

[Finomosec]

I think i found a solution. The only thing is ... its result differs from the PL/SQL-function above. I have to look into this again.

But anyway ... here is my solution to group sequential numbers with SQL:

select
    min(beginning) beginning, -- #4
    max(endnr) endnr, -- #4
    row_number() over (order by grp),
    grp,
    count(*)
from (
    select
        s.*,
        SUM(diff) OVER (order by beginning) grp -- #2
    from (
        select
            beginning,
            endnr,
            beginning - NVL(LAG(endnr + 1) OVER (ORDER BY beginning asc), beginning) diff -- #1
        FROM numbers
    ) s
)
group by grp -- #3
order by grp asc;

1. calculate the difference between the previous endnr +1 and the current beginning (this is 0 if they are consecutive)
2. in a surrounding query calculate the sum of all previous diff-values (this is number increasing on non-consecutive numbers and staying the same on consecutive ones)
3. group by the calculated sum (grp)
4. get min/max or whatever needed from the group

Warning: The beginning and endnr have to be numerical!! (use TO_NUMBER(varchar_column) if needed).

The values "row_number() over (order by grp)", "grp" and "count(*)" are only for demonstration purpose and can be removed afterwards.

Greetings Finomosec;