By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,676 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,676 IT Pros & Developers. It's quick & easy.

Grouping sequential numbers with SQL

P: 7
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;
Jul 6 '07 #1
Share this Question
Share on Google+
3 Replies


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

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TYPE beg_end AS OBJECT (beginning VARCHAR2(15), endnr varchar(15));
  2. /
  3.  
  4. CREATE OR REPLACE TYPE beg_end_set IS TABLE OF beg_end;
  5. /
  6.  
  7. CREATE OR REPLACE FUNCTION groupNumbers RETURN beg_end_set PIPELINED IS
  8.     CURSOR pns IS SELECT * FROM numbers ORDER BY beginning ASC;
  9.     res beg_end := beg_end(null, null);
  10.     curPN numbers%ROWTYPE;
  11.     beginning VARCHAR2(15) := null;
  12.     endnr VARCHAR2(15) := null;
  13. BEGIN
  14.     OPEN pns;
  15.     LOOP
  16.         FETCH pns INTO curPN;
  17.         EXIT WHEN pns%NOTFOUND;
  18.         IF beginning IS NULL THEN
  19.             beginning := curPN.beginning;
  20.         ELSIF to_number(endnr) + 1 != to_number(curPN.beginning) THEN
  21.             res.beginning := beginning;
  22.             res.endnr := endnr;
  23.             PIPE row(res);
  24.             beginning := curPN.beginning;
  25.         END IF;
  26.         endnr := curPN.beginning;
  27.     END LOOP;
  28.     IF beginning IS NOT NULL THEN
  29.         res.beginning := beginning;
  30.         res.endnr := endnr;
  31.         PIPE row(res);
  32.     END IF;
  33.     CLOSE pns;
  34.     return;
  35. END;
  36. /
  37. SHOW ERRORS
  38.  
  39.  
  40. -- then it can be used like this ...
  41. select count(*) from table(groupNumbers);
  42.  
  43. -- count for diff ...
  44. select count(*) from numbers;
Jul 6 '07 #2

P: 7
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT min(beginning), max(endnr)
  2. FROM numbers n
  3. group by where (prev.endnr +1 = n.beginning);
  4.  
Maybe its just another syntax to use ...

Any ideas?
Jul 9 '07 #3

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. select
  2.     min(beginning) beginning, -- #4
  3.     max(endnr) endnr, -- #4
  4.     row_number() over (order by grp),
  5.     grp,
  6.     count(*)
  7. from (
  8.     select
  9.         s.*,
  10.         SUM(diff) OVER (order by beginning) grp -- #2
  11.     from (
  12.         select
  13.             beginning,
  14.             endnr,
  15.             beginning - NVL(LAG(endnr + 1) OVER (ORDER BY beginning asc), beginning) diff -- #1
  16.         FROM numbers
  17.     ) s
  18. )
  19. group by grp -- #3
  20. 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;
Jul 9 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.