## Question and Answer

## 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.

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

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

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

# Comments

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.

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.

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).

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).

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

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

( 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>

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;

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;

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>

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>

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>

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)...

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>

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 11This 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 11When 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 ;)

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 );