Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

To select a series of consecutive numbers with their position

RanagalAug 23 2019 — edited Aug 26 2019

Hello experts,

DB Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

I have a requirement to select a series of consecutive numbers of a specified length in a specified position. By position I mean the occurrences of specified length of such series of numbers. I have done something like below. Please let me know if it needs further improvements.

with

inputs(ids, nums) as

(

select 1, 1 from dual union all

select 2, 2 from dual union all

select 3, 3 from dual union all

select 4, 11 from dual union all

select 5, 4 from dual union all

select 6, 5 from dual union all

select 7, 6 from dual union all

select 8, 8 from dual union all

select 9, 9 from dual

)

select ids, nums from

(

    select * from inputs

    match_recognize

    (

    order by ids

    measures match_number() as mn

    all rows per match

    after match skip to last s

    pattern (a b{:length_of_series} ({-c-}|$)) -- 1 if length is 2

    subset s = (c,b)

    define  b as nums = prev(nums) + 1,

            c as nums <> b.nums + 1 or c.nums is null

    )

    where mn = :which_position

);

For eg;

In the above data, you will find that a series of numbers of length 3 is present twice. 1, 2, 3 and then 4,5,6. So, if I input the length of series as 3 and the position, I mean the occurrence,  as 2 then I will get 4,5,6 as the result and if I input occurrence of such a length is 1 then I will get 1,2,3.

I am getting the result as expected. But my questions:

1. Am I doing it correctly ?

2. Are there any hidden scenarios that I am missing

3. Is there a better way of doing the same ?

Regards,

Ranagal

This post has been answered by mathguy on Aug 24 2019
Jump to Answer
Comments
Post Details
Added on Aug 23 2019
45 comments
2,684 views