Skip to Main Content
  • Questions
  • Query to partition the range of sequential numbers.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Raghavendra .

Asked: February 01, 2010 - 7:58 am UTC

Last updated: February 12, 2013 - 7:35 am UTC

Version: 11.1.0.6.0

Viewed 1000+ times

You Asked

Hi Tom,

create table RAND
(
N NUMBER
)
/
insert into rand (N)
values (1)
/
insert into rand (N)
values (2)
/
insert into rand (N)
values (3)
/
insert into rand (N)
values (5)
/
insert into rand (N)
values (7)
/
insert into rand (N)
values (8)
/
insert into rand (N)
values (9)
/
insert into rand (N)
values (11)
/
commit
/
So the data will be
N
--
1
2
3
5
7
8
9
11

I need to partition the numbers range, based on the next non-sequential numbers.The desired output has to be.

N X
--------
1
2
3 1-3
5 5
7
8 7-8
9 9
11 11.

I have written the query to parition, till what point i have to do, but i am
not able to get the exact logic as to how to do this.

SELECT n,
CASE
WHEN n + 1 = nvl(lead(n) over(ORDER BY n), 0) THEN
NULL
ELSE
'Till here'
END AS x
FROM rand
ORDER BY n;

N X
------------------
1
2
3 Till here
5 Till here
7
8
9 Till here
11 Till here

I am not getting the idea to partition(give a rank) the range of numbers from where its not sequential.

Your approach towards the solutions is amazing and smarter than all of us!

Please guide me as to how to solve this.
(May be we have to use rollup or grouping functions ?)

Thanks a lot for the website and help.

Regards
Raghavendra Boralli

and Tom said...

ops$tkyte%ORA10GR2> select n,
  2         case when end_grp = 'x' then last_value(begin_grp ignore nulls) over (order by n) end s,
  3         case when end_grp = 'x' then n end e
  4    from (
  5  select n,
  6         case when nvl(lag(n) over (order by n),n) <> n-1 then n end begin_grp,
  7             case when nvl(lead(n) over (order by n),n) <> n+1 then 'x' end end_grp
  8    from rand
  9         )
 10  /

         N          S          E
---------- ---------- ----------
         1
         2
         3          1          3
         5          5          5
         7
         8
         9          7          9
        11         11         11

8 rows selected.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question on last_value

satyadeep chandrashekar, February 09, 2010 - 7:50 pm UTC

Would appreciate your help in understanding how last_value works -

N
--
1
2
3
5
6
7

The result of inner select provided by you -

N B E
- - -
1 1
2
3 x
5 5
6
7 x

The outer select with last_value is able to group 1,2,3 and get last value as 1 and group 5,6,7 and get last value as 5. The over(...) does not specify any partition. Can you please explain how last_value works? To me, it seems it always return 1 since I am not sure how the grouping is happening.

Thanks,
Satya.

Tom Kyte
February 15, 2010 - 3:01 pm UTC

last_value(begin_grp ignore nulls) over (order by n)


that has an implicit partition of "entire set"
it has an implicit window of "current row and unbounded rows preceding"

so, it takes the entire set and sorts it by N. Then, for the first row, it takes the last value (ignoring nulls) starting from the current row (row 1 after sorting by n) and all preceding rows (eg: it takes the last NON NULL value of begin_grp from the current row backwards, up, in the result set).

Another way

Martijn, February 16, 2010 - 5:08 am UTC

Using a bit of Aketi's "Tabibitosan-method" ;)

( http://forums.oracle.com/forums/thread.jspa?messageID=3989678 )

SQL> select n
  2  ,     case 
  3          when n = max(n) over (partition by grp) 
  4          then min(n) over (partition by grp)
  5        end s
  6  ,     case 
  7          when n = max(n) over (partition by grp) 
  8           and min(n) over (partition by grp) <> max(n) over (partition by grp)
  9          then max(n) over (partition by grp)        
 10        end e
 11  from ( select n
 12         ,      n-row_number() over (order by n) grp
 13         from   rand
 14       );

         N          S          E
---------- ---------- ----------
         1
         2
         3          1          3
         5          5
         7
         8
         9          7          9
        11         11

8 rows selected.

SQL> 


Another way to use the last_value function

Alan Stewart, March 11, 2010 - 10:07 am UTC

create table RAND as
select level N from dual connect by level < 12;

delete from RAND where N in (4,6,10);

with R as
(select
n,
lag (n,1,n) over (order by n) lagn,
lead(n,1,n) over (order by n) leadn,
case when lag (n,1,n) over (order by n) <> n-1 then n end gmin,
case when lead(n,1,n) over (order by n) <> n+1 then n end gmax
from RAND
)
select
n, lagn, leadn, gmin, gmax,
last_value (gmin ignore nulls) over (order by n) first_in_group,
last_value (gmax ignore nulls) over (order by n desc) last_in_group
from R
order by n;

And yet another method

isotope, May 30, 2012 - 12:27 pm UTC

SQL>
SQL>
SQL> --
SQL> SELECT *
  2    FROM rand
  3  /

         N
----------
         1
         2
         3
         5
         7
         8
         9
        11

8 rows selected.

SQL>
SQL>
SQL> --
SQL> SELECT n,
  2         CASE diff - LEAD (diff) OVER (ORDER BY 1)
  3           WHEN -1
  4             THEN MIN (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
  5           WHEN NULL
  6             THEN n
  7         END AS strt,
  8         CASE diff - LEAD (diff) OVER (ORDER BY 1)
  9           WHEN -1
 10             THEN MAX (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
 11           WHEN NULL
 12             THEN n
 13         END AS fin
 14    FROM (
 15          SELECT n,
 16                 n - ROW_NUMBER () OVER (ORDER BY n) AS diff
 17            FROM rand x
 18         ) t
 19  /

         N       STRT        FIN
---------- ---------- ----------
         1
         2
         3          1          3
         5          5          5
         7
         8
         9          7          9
        11

8 rows selected.

SQL>
SQL>
SQL>
SQL>

Sorry, the one above is incorrect!

isotope, May 30, 2012 - 12:33 pm UTC

SQL>
SQL>
SQL> --
SQL> SELECT *
  2    FROM rand
  3  /

         N
----------
         1
         2
         3
         5
         7
         8
         9
        11

8 rows selected.

SQL>
SQL>
SQL> --
SQL> SELECT n,
  2         CASE
  3           WHEN diff <> LEAD (diff) OVER (ORDER BY 1)
  4             THEN MIN (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
  5           WHEN LEAD (diff) OVER (ORDER BY 1) is null
  6             THEN n
  7         END AS strt,
  8         CASE
  9           WHEN diff <> LEAD (diff) OVER (ORDER BY 1)
 10             THEN MAX (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
 11           WHEN LEAD (diff) OVER (ORDER BY 1) is null
 12             THEN n
 13         END AS fin
 14    FROM (
 15          SELECT n,
 16                 n - ROW_NUMBER () OVER (ORDER BY n) AS diff
 17            FROM rand x
 18         ) t
 19  /

         N       STRT        FIN
---------- ---------- ----------
         1
         2
         3          1          3
         5          5          5
         7
         8
         9          7          9
        11         11         11

8 rows selected.

SQL>
SQL>
SQL>
SQL>

another method

igor, February 11, 2013 - 5:57 pm UTC

just wanted to share another method - works for me in 10.2.0.4
(honestly though, I'm still not 100% sure if rownum is OK here)

SQL> create table rand as
  2  select 12 - level n
  3  from dual
  4  connect by level < 12;

Table created.

SQL> delete from RAND where N in (4,6,10);

3 rows deleted.

SQL> select * from rand;

         N
----------
        11
         9
         8
         7
         5
         3
         2
         1

8 rows selected.

SQL> select n,
  2    case when
  3      last_value(n) over (partition by n-rownum order by n rows
  4        between unbounded preceding and unbounded following) = n
  5         then
  6      first_value(n) over (partition by n-rownum order by n rows
  7        between unbounded preceding and unbounded following)
  8         else
  9      null end strt,
 10    case when
 11      last_value(n) over (partition by n-rownum order by n rows
 12        between unbounded preceding and unbounded following) = n
 13         then
 14      last_value(n) over (partition by n-rownum order by n rows
 15        between unbounded preceding and unbounded following)
 16         else
 17      null end fin
 18  from (select * from rand order by n);

         N       STRT        FIN
---------- ---------- ----------
         1
         2
         3          1          3
         5          5          5
         7
         8
         9          7          9
        11         11         11

8 rows selected.

SQL>

Tom Kyte
February 12, 2013 - 7:35 am UTC

I'm not sure about the rownum usage here either. rownum has to be assigned before the order by in the analytic - and the order by in the subquery is free to be optimized away (unless it was a top-n query - where rownum < :x)...


another method with model clause

igor, February 15, 2013 - 9:52 pm UTC

Thanks Tom. It seems I should use rownum more carefully indeed ..

By the way, I just found how simply this can be done with model clause - no aggregates, no analytics.

SQL> SELECT n, strt, fin
  2    FROM rand
  3   MODEL
  4     DIMENSION BY (n)
  5     MEASURES (0 strt, 0 fin)
  6     RULES( strt[n] ORDER BY n = NVL(strt[CV()-1], CV(n)),
  7            strt[n] ORDER BY n = NULLIF(strt[CV()], strt[CV()+1]),
  8            fin[n] = CASE WHEN strt[CV()] IS NULL THEN NULL ELSE CV(n) END
  9     )
 10  ORDER BY 1;

         N       STRT        FIN
---------- ---------- ----------
         1
         2
         3          1          3
         5          5          5
         7
         8
         9          7          9
        11         11         11

8 rows selected.

SQL> 

Another method with version 12c

Stew Ashton, September 27, 2013 - 7:53 am UTC


Now that we have pattern matching available, many problems appear to be pattern matching exercises.
SELECT * FROM rand
MATCH_RECOGNIZE (
  ORDER BY n
  MEASURES A.n an, last(b.n) bn
  PATTERN (A b*)
  DEFINE b AS b.n = prev(b.n)+1
);

        AN         BN
---------- ----------
         1          3 
         5            
         7          9 
        11
This answers the question with the default "one row per match". Since the OP wants every row, with nulls in all but the last row, I have to do some formatting:
SELECT * FROM rand
MATCH_RECOGNIZE (
  ORDER BY n
  MEASURES
    decode(n, nvl(FINAL LAST(b.n), A.n), A.n) an,
    decode(n, nvl(final last(b.n), a.n), final last(b.n)) bn
  ALL ROWS PER MATCH
  PATTERN (A b*)
  DEFINE b AS b.n = prev(b.n)+1
);

         N         AN         BN
---------- ---------- ----------
         1                       
         2                       
         3          1          3 
         5          5            
         7                       
         8                       
         9          7          9 
        11         11
When getting "ALL ROWS PER MATCH" you have to choose between RUNNING and FINAL semantics (a sort of shorthand windowing clause).

Both times I leave out the "AFTER MATCH SKIP PAST LAST ROW" clause, which is the default.

I think Tom is going to be less motivated to learn the MODEL clause now ;)

Another method with version 12c: improvement

Stew Ashton, September 27, 2013 - 11:07 am UTC


This is a cosmetic change: the SUBSET clause lets me avoid the NVL() stuff in the MEASURES clause.
SELECT * FROM rand
MATCH_RECOGNIZE (
  ORDER BY n
  MEASURES
    decode(n, FINAL LAST(ab.n), A.n) an,
    decode(n, FINAL LAST(ab.n), final last(b.n)) bn
  ALL ROWS PER MATCH
  PATTERN (A B*)
  SUBSET AB = (A,B)
  DEFINE b AS b.n = prev(ab.n)+1
);

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.