Home » SQL & PL/SQL » SQL & PL/SQL » how to incrementally get all numbers incremented by n between 2 columns values (oracle 19c)
( ) 1 Vote
| how to incrementally get all numbers incremented by n between 2 columns values [message #687342] |
Mon, 27 February 2023 22:36  |
 |
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
HI, i have below query and it returns below values incremented by 1. but i need to get incremented by 2 or 3 not able to figure out how to do.
select inst_num, start_snap_id, end_snap_id
from (
select s.instance_number as inst_num,
s.snap_id as start_snap_id,
lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id
from dba_hist_snapshot s
where
s.snap_id >= 165952
and s.snap_id <= 165959
)
where end_snap_id is not null
order by inst_num, start_snap_id;
1 165952 165953
1 165953 165954
1 165954 165955
1 165955 165956
1 165956 165957
1 165957 165958
2 165952 165953
2 165953 165954
2 165954 165955
2 165955 165956
2 165956 165957
2 165957 165958
i want values like below
1 165952 165954
1 165954 165956
1 165956 165958
1 165958 165959
2 165952 165954
2 165954 165956
2 165956 165958
2 165958 165959
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687343 is a reply to message #687342] |
Tue, 28 February 2023 00:22  |
 |
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
You are very close to the solution just number the rows in the subquery and restrict the result to 1 out of 2:
SQL> with
2 data as (
3 select s.instance_number as inst_num, s.snap_id as start_snap_id,
4 lead(s.snap_id,1,null) over
5 (partition by s.instance_number order by s.snap_id)
6 as end_snap_id,
7 row_number() over (partition by s.instance_number order by s.snap_id)
8 as rn
9 from dba_hist_snapshot s
10 where s.snap_id between 53100 and 53110
11 )
12 select inst_num, start_snap_id, end_snap_id
13 from data
14 where end_snap_id is not null and mod(rn,2) = 1
15 order by inst_num, start_snap_id
16 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53101
1 53102 53103
1 53104 53105
1 53108 53109
4 rows selected.
SQL> def n=2
SQL> with
2 data as (
3 select s.instance_number as inst_num, s.snap_id as start_snap_id,
4 lead(s.snap_id,&n-1,null) over
5 (partition by s.instance_number order by s.snap_id)
6 as end_snap_id,
7 row_number() over (partition by s.instance_number order by s.snap_id)
8 as rn
9 from dba_hist_snapshot s
10 where s.snap_id between 53100 and 53110
11 )
12 select inst_num, start_snap_id, end_snap_id
13 from data
14 where end_snap_id is not null and mod(rn,&n) = 1
15 order by inst_num, start_snap_id
16 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53101
1 53102 53103
1 53104 53105
1 53108 53109
4 rows selected.
SQL> def n=3
SQL> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53102
1 53103 53105
1 53108 53110
3 rows selected.
SQL> def n=4
SQL> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53103
1 53104 53109
2 rows selected. |
|
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687345 is a reply to message #687344] |
Tue, 28 February 2023 09:27 |
|
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
if i use your query like below i am getting exactly what i wanted but then issue i am facing last number
def n=3
with
data as (
select s.instance_number as inst_num, s.snap_id as start_snap_id,
lead(s.snap_id,&n-1,null) over
(partition by s.instance_number order by s.snap_id)
as end_snap_id,
row_number() over (partition by s.instance_number order by s.snap_id)
as rn
from dba_hist_snapshot s
where s.snap_id between 165952 and 165959
)
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,&n-1) = 1
order by inst_num, start_snap_id
/
1 165952 165954
1 165954 165956
1 165956 165958
but it does not returns the last rows i mean
1 165958 165959
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687346 is a reply to message #687345] |
Tue, 28 February 2023 10:35 |
 |
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950
1 165951
1 165952
1 165953
1 165954
1 165955
1 165956
1 165957
1 165958
1 165959
2 165949
2 165950
2 165951
2 165952
2 165953
2 165954
2 165955
2 165956
2 165957
2 165958
2 165959
22 rows selected.
SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
2 from (select s.instance_number as inst_num,
3 s.snap_id as start_snap_id,
4 lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
5 row_number() over (partition by s.instance_number order by s.snap_id) as rn
6 from test_data s
7 where s.snap_id between 165952 and 165959)
8 where end_snap_id is not null and mod(rn,&n) = 1
9 order by inst_num, start_snap_id
10 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165954
1 165954 165956
1 165956 165958
2 165952 165954
2 165954 165956
2 165956 165958
6 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
2 from (select s.instance_number as inst_num,
3 s.snap_id as start_snap_id,
4 lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
5 row_number() over (partition by s.instance_number order by s.snap_id) as rn
6 from test_data s
7 where s.snap_id between 165952 and 165959)
8 where end_snap_id is not null and mod(rn,&n) = 1
9 order by inst_num, start_snap_id
10 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165955
1 165955 165958
2 165952 165955
2 165955 165958
4 rows selected.
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687347 is a reply to message #687346] |
Tue, 28 February 2023 10:40 |
|
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
Barbara Boehmer wrote on Tue, 28 February 2023 10:35
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950
1 165951
1 165952
1 165953
1 165954
1 165955
1 165956
1 165957
1 165958
1 165959
2 165949
2 165950
2 165951
2 165952
2 165953
2 165954
2 165955
2 165956
2 165957
2 165958
2 165959
22 rows selected.
SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
2 from (select s.instance_number as inst_num,
3 s.snap_id as start_snap_id,
4 lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
5 row_number() over (partition by s.instance_number order by s.snap_id) as rn
6 from test_data s
7 where s.snap_id between 165952 and 165959)
8 where end_snap_id is not null and mod(rn,&n) = 1
9 order by inst_num, start_snap_id
10 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165954
1 165954 165956
1 165956 165958
2 165952 165954
2 165954 165956
2 165956 165958
6 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
2 from (select s.instance_number as inst_num,
3 s.snap_id as start_snap_id,
4 lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
5 row_number() over (partition by s.instance_number order by s.snap_id) as rn
6 from test_data s
7 where s.snap_id between 165952 and 165959)
8 where end_snap_id is not null and mod(rn,&n) = 1
9 order by inst_num, start_snap_id
10 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165955
1 165955 165958
2 165952 165955
2 165955 165958
4 rows selected.
it is the same issue. it does not covered last number from the list. last line has to be
1 165958 165959 but it is not.
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687348 is a reply to message #687345] |
Tue, 28 February 2023 10:52 |
|
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please read How to use [code] tags and make your code easier to read.
Quote:but it is not what i needed. your version returns
I have non consecutive snap_id, some have been deleted.
Quote:but it does not returns the last rows i mean...
This row does not fit your rule: grouping by 3 rows then selecting those where end_date is not null.
If you want incomplete groups then you have to either dynamically create the query if <n> is dynamic.
For instance using SQL*Plus, you build the query in substitution variables then execution this later.
First I create, in sql_beg, the beginning of the final query which does not depend on <n>:
SQL> col sql_beg new_value sql_beg
SQL> select q'[ data as (
2 select s.instance_number as inst_num, s.snap_id as start_snap_id,
3 coalesce(
4 ]' sql_beg
5 from dual
6 /
SQL_BEG
--------------------------------------------------------------------------
data as (
select s.instance_number as inst_num, s.snap_id as start_snap_id,
coalesce(
Then I create the parts that depend on <n>: the parameters of the COALESCE function and the end of the query:
SQL> col sql_mid new_value sql_mid
SQL> col sql_end new_value sql_end
SQL> def n=3
SQL> select listagg('lead(s.snap_id,&n-'||level||',null) over
2 (partition by s.instance_number order by s.snap_id)',',
3 ') within group (order by null) sql_mid
4 from dual
5 connect by level < &n
6 /
SQL_MID
--------------------------------------------------------------------------------------
lead(s.snap_id,3-1,null) over
(partition by s.instance_number order by s.snap_id),
lead(s.snap_id,3-2,null) over
(partition by s.instance_number order by s.snap_id)
SQL> select q'[) as end_snap_id,
2 row_number() over (partition by s.instance_number order by s.snap_id)
3 as rn
4 from dba_hist_snapshot s
5 where s.snap_id between 53100 and 53111
6 )
7 select inst_num, start_snap_id, end_snap_id
8 from data
9 where end_snap_id is not null and mod(rn,&n) = 1
10 order by inst_num, start_snap_id
11 ]' sql_end
12 from dual
13 /
SQL_END
--------------------------------------------------------------------------------------
) as end_snap_id,
row_number() over (partition by s.instance_number order by s.snap_id)
as rn
from dba_hist_snapshot s
where s.snap_id between 53100 and 53111
)
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,3) = 1
order by inst_num, start_snap_id
SQL> with &sql_beg &sql_mid &sql_end
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53102
1 53103 53105
1 53108 53110
3 rows selected.
SQL> def n=4
SQL> select listagg('lead(s.snap_id,&n-'||level||',null) over
2 (partition by s.instance_number order by s.snap_id)',',
3 ') within group (order by null) sql_mid
4 from dual
5 connect by level < &n
6 /
SQL_MID
----------------------------------------------------------------------------------------------
lead(s.snap_id,4-1,null) over
(partition by s.instance_number order by s.snap_id),
lead(s.snap_id,4-2,null) over
(partition by s.instance_number order by s.snap_id),
lead(s.snap_id,4-3,null) over
(partition by s.instance_number order by s.snap_id)
SQL> select q'[) as end_snap_id,
2 row_number() over (partition by s.instance_number order by s.snap_id)
3 as rn
4 from dba_hist_snapshot s
5 where s.snap_id between 53100 and 53111
6 )
7 select inst_num, start_snap_id, end_snap_id
8 from data
9 where end_snap_id is not null and mod(rn,&n) = 1
10 order by inst_num, start_snap_id
11 ]' sql_end
12 from dual
13 /
SQL_END
----------------------------------------------------------------------------------------------
) as end_snap_id,
row_number() over (partition by s.instance_number order by s.snap_id)
as rn
from dba_hist_snapshot s
where s.snap_id between 53100 and 53111
)
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,4) = 1
order by inst_num, start_snap_id
SQL> with &sql_beg &sql_mid &sql_end
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53103
1 53104 53109
1 53110 53111
3 rows selected.
[Edit] Note: You can do it as well in PL/SQL if you don't need "pure" SQL.
As Barbara is there, maybe she would like to provide the solution. 
[Updated on: Tue, 28 February 2023 10:56] Report message to a moderator |
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687349 is a reply to message #687348] |
Tue, 28 February 2023 13:12 |
|
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's a PL/SQL version.
As I don't like dbms_output, I return the result in a cursor, you can print with SQL*Plus or with your client program (begin and end snap ids are also in substitution variables):
SQL> def snap_beg = 53100
SQL> def snap_end = 53111
SQL> def n = 2
SQL> var c refcursor
SQL> Declare
2 sql_beg varchar2(256) :=
3 q'[with
4 data as (
5 select s.instance_number as inst_num, s.snap_id as start_snap_id,
6 coalesce(
7 ]';
8 sql_mid varchar2(1000);
9 sql_end varchar2(500) :=
10 q'[) as end_snap_id,
11 row_number() over (partition by s.instance_number order by s.snap_id)
12 as rn
13 from dba_hist_snapshot s
14 where s.snap_id between &snap_beg and &snap_end
15 )
16 select inst_num, start_snap_id, end_snap_id
17 from data
18 where end_snap_id is not null and mod(rn,&n) = 1
19 order by inst_num, start_snap_id
20 ]';
21 t number; -- test variable
22 begin
23 t := to_number('&n');
24 if &n != trunc(&n) then
25 raise_application_error (-20000, 'n should be an integer');
26 end if;
27 if &n < 2 then
28 raise_application_error (-20001, 'n should be greater than or equal to 2');
29 end if;
30 if &n = 2 then
31 open :c for
32 q'[with
33 data as (
34 select s.instance_number as inst_num, s.snap_id as start_snap_id,
35 lead(s.snap_id,1,null) over
36 (partition by s.instance_number order by s.snap_id)
37 as end_snap_id,
38 row_number() over (partition by s.instance_number order by s.snap_id)
39 as rn
40 from dba_hist_snapshot s
41 where s.snap_id between 53100 and 53110
42 )
43 select inst_num, start_snap_id, end_snap_id
44 from data
45 where end_snap_id is not null and mod(rn,2) = 1
46 order by inst_num, start_snap_id]';
47 else
48 for i in 1..&n-1 loop
49 sql_mid := sql_mid ||
50 'lead(s.snap_id,&n-'||i||',null) over
51 (partition by s.instance_number order by s.snap_id),';
52 end loop;
53 sql_mid := rtrim(sql_mid,',');
54 open :c for sql_beg||sql_mid||sql_end;
55 end if;
56 end;
57 /
PL/SQL procedure successfully completed.
SQL> print c
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53101
1 53102 53103
1 53104 53105
1 53108 53109
4 rows selected.
SQL> def n=3
SQL> /
PL/SQL procedure successfully completed.
SQL> print c
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53102
1 53103 53105
1 53108 53110
3 rows selected.
SQL> def n=4
SQL> /
PL/SQL procedure successfully completed.
SQL> print c
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53100 53103
1 53104 53109
1 53110 53111
3 rows selected. |
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687350 is a reply to message #687348] |
Tue, 28 February 2023 13:14 |
|
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
Thank you so much Michel you are awesome!!!. i changed your query like below and i am getting exactly what i wanted.
with data as (
select s.instance_number as inst_num, s.snap_id as start_snap_id,
coalesce(lead(s.snap_id,&n,null) over(partition by s.instance_number order by s.snap_id),lead(s.snap_id,mod(165967-165952,&n-1),null) over (partition by s.instance_number order by s.snap_id)
) as end_snap_id,
row_number() over (partition by s.instance_number order by s.snap_id)
as rn
from dba_hist_snapshot s
where s.snap_id between 165952 and 165967
)
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,&n) = 1
order by inst_num, start_snap_id
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687351 is a reply to message #687350] |
Tue, 28 February 2023 13:30 |
|
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I see some problems with that. Please see my tests below using various number values for n, including 1, which returns no rows.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950
1 165951
1 165952
1 165953
1 165954
1 165955
1 165956
1 165957
1 165958
1 165959
2 165949
2 165950
2 165951
2 165952
2 165953
2 165954
2 165955
2 165956
2 165957
2 165958
2 165959
22 rows selected.
SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 1
SCOTT@orcl_12.1.0.2.0> with data as (
2 select s.instance_number as inst_num, s.snap_id as start_snap_id,
3 coalesce(lead(s.snap_id,&n,null) over(partition by s.instance_number order by s.snap_id),lead(s.snap_id,mod(165967-165952,&n-1),null) over (partition by s.instance_number order by s.snap_id)
4 ) as end_snap_id,
5 row_number() over (partition by s.instance_number order by s.snap_id)
6 as rn
7 from test_data s
8 where s.snap_id between 165952 and 165967
9 )
10 select inst_num, start_snap_id, end_snap_id
11 from data
12 where end_snap_id is not null and mod(rn,&n) = 1
13 order by inst_num, start_snap_id
14 /
no rows selected
SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165954
1 165954 165956
1 165956 165958
1 165958 165958
2 165952 165954
2 165954 165956
2 165956 165958
2 165958 165958
8 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165955
1 165955 165958
1 165958 165959
2 165952 165955
2 165955 165958
2 165958 165959
6 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 4
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165956
1 165956 165956
2 165952 165956
2 165956 165956
4 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 5
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165957
2 165952 165957
2 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 6
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165958
1 165958 165958
2 165952 165958
2 165958 165958
4 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 7
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165959
2 165952 165959
2 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 8
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165953
2 165952 165953
2 rows selected.
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687352 is a reply to message #687349] |
Tue, 28 February 2023 13:31 |
|
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Or, if you prefer, creating a function taking the beginning and end snap ids and the number of rows in the group:
SQL> Create or replace function get_snap_groups
2 (snap_beg integer, snap_end integer, n integer)
3 return sys_refcursor
4 is
5 sql_beg varchar2(256);
6 sql_mid varchar2(1000);
7 sql_end varchar2(500);
8 res sys_refcursor;
9 begin
10 if n < 2 then
11 raise_application_error (-20001, 'n should be greater than or equal to 2');
12 end if;
13 if n = 2 then
14 open res for
15 'with
16 data as (
17 select s.instance_number as inst_num, s.snap_id as start_snap_id,
18 lead(s.snap_id,1,null) over
19 (partition by s.instance_number order by s.snap_id)
20 as end_snap_id,
21 row_number() over (partition by s.instance_number order by s.snap_id)
22 as rn
23 from dba_hist_snapshot s
24 where s.snap_id between '||snap_beg||' and '||snap_end||'
25 )
26 select inst_num, start_snap_id, end_snap_id
27 from data
28 where end_snap_id is not null and mod(rn,2) = 1
29 order by inst_num, start_snap_id';
30 else
31 sql_beg :=
32 'with
33 data as (
34 select s.instance_number as inst_num, s.snap_id as start_snap_id,
35 coalesce(
36 ';
37 sql_mid := '';
38 for i in 1..n-1 loop
39 sql_mid := sql_mid ||
40 'lead(s.snap_id,'||to_char(n-i)||',null) over
41 (partition by s.instance_number order by s.snap_id),';
42 end loop;
43 sql_mid := rtrim(sql_mid,',');
44 sql_end :=
45 ') as end_snap_id,
46 row_number() over (partition by s.instance_number order by s.snap_id)
47 as rn
48 from dba_hist_snapshot s
49 where s.snap_id between '||snap_beg||' and '||snap_end||'
50 )
51 select inst_num, start_snap_id, end_snap_id
52 from data
53 where end_snap_id is not null and mod(rn,'||n||') = 1
54 order by inst_num, start_snap_id
55 ';
56 open res for sql_beg||sql_mid||sql_end;
57 end if;
58 return res;
59 end;
60 /
Function created.
SQL> exec :c := get_snap_groups(53110, 53123, 2)
PL/SQL procedure successfully completed.
SQL> print c
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53111
1 53112 53113
1 53114 53115
1 53116 53117
1 53118 53119
1 53120 53121
1 53122 53123
7 rows selected.
SQL> exec :c := get_snap_groups(53110, 53123, 3)
PL/SQL procedure successfully completed.
SQL> print c
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53112
1 53113 53115
1 53116 53118
1 53119 53121
1 53122 53123
5 rows selected.
SQL> exec :c := get_snap_groups(53110, 53123, 4)
PL/SQL procedure successfully completed.
SQL> print c
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53113
1 53114 53117
1 53118 53121
1 53122 53123
4 rows selected.
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687353 is a reply to message #687350] |
Tue, 28 February 2023 13:32 |
|
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is another possible version with tests of various number values for n.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950
1 165951
1 165952
1 165953
1 165954
1 165955
1 165956
1 165957
1 165958
1 165959
2 165949
2 165950
2 165951
2 165952
2 165953
2 165954
2 165955
2 165956
2 165957
2 165958
2 165959
22 rows selected.
SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 1
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
2 from (select instance_number as inst_num, snap_id as start_snap_id,
3 lead(snap_id) over (partition by instance_number order by snap_id) as end_snap_id
4 from (select instance_number, snap_id
5 from (select instance_number, snap_id,
6 row_number() over (partition by instance_number order by snap_id) rn
7 from test_data
8 where snap_id between 165952 and 165959)
9 start with rn = 1
10 connect by prior instance_number = instance_number and prior rn = rn - &n
11 union
12 select instance_number, max(snap_id) as snap_id
13 from test_data
14 group by instance_number))
15 where end_snap_id is not null
16 order by inst_num, start_snap_id
17 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165953
1 165953 165954
1 165954 165955
1 165955 165956
1 165956 165957
1 165957 165958
1 165958 165959
2 165952 165953
2 165953 165954
2 165954 165955
2 165955 165956
2 165956 165957
2 165957 165958
2 165958 165959
14 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165954
1 165954 165956
1 165956 165958
1 165958 165959
2 165952 165954
2 165954 165956
2 165956 165958
2 165958 165959
8 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165955
1 165955 165958
1 165958 165959
2 165952 165955
2 165955 165958
2 165958 165959
6 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 4
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165956
1 165956 165959
2 165952 165956
2 165956 165959
4 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 5
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165957
1 165957 165959
2 165952 165957
2 165957 165959
4 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 6
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165958
1 165958 165959
2 165952 165958
2 165958 165959
4 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 7
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165959
2 165952 165959
2 rows selected.
SCOTT@orcl_12.1.0.2.0> def n = 8
SCOTT@orcl_12.1.0.2.0> /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 165952 165959
2 165952 165959
2 rows selected.
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687354 is a reply to message #687352] |
Tue, 28 February 2023 13:50 |
|
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Or with a pipelined function:
SQL> Create or replace type snap_group_type as object (
2 inst_num integer,
3 start_snap_id integer,
4 end_snap_id integer
5 )
6 /
Type created.
SQL> Create or replace type snap_group_array is table of snap_group_type
2 /
Type created.
SQL> Create or replace function get_snap_groups
2 (snap_beg integer, snap_end integer, n integer)
3 return snap_group_array pipelined
4 is
5 sql_beg varchar2(256);
6 sql_mid varchar2(1000);
7 sql_end varchar2(500);
8 sql_str varchar2(2000);
9 res sys_refcursor;
10 rec snap_group_type;
11 begin
12 if n < 2 then
13 raise_application_error (-20001, 'n should be greater than or equal to 2');
14 end if;
15 if n = 2 then
16 sql_str :=
17 'with
18 data as (
19 select s.instance_number as inst_num, s.snap_id as start_snap_id,
20 lead(s.snap_id,1,null) over
21 (partition by s.instance_number order by s.snap_id)
22 as end_snap_id,
23 row_number() over (partition by s.instance_number order by s.snap_id)
24 as rn
25 from dba_hist_snapshot s
26 where s.snap_id between '||snap_beg||' and '||snap_end||'
27 )
28 select snap_group_type(inst_num, start_snap_id, end_snap_id)
29 from data
30 where end_snap_id is not null and mod(rn,2) = 1
31 order by inst_num, start_snap_id';
32 else
33 sql_beg :=
34 'with
35 data as (
36 select s.instance_number as inst_num, s.snap_id as start_snap_id,
37 coalesce(
38 ';
39 sql_mid := '';
40 for i in 1..n-1 loop
41 sql_mid := sql_mid ||
42 'lead(s.snap_id,'||to_char(n-i)||',null) over
43 (partition by s.instance_number order by s.snap_id),';
44 end loop;
45 sql_mid := rtrim(sql_mid,',');
46 sql_end :=
47 ') as end_snap_id,
48 row_number() over (partition by s.instance_number order by s.snap_id)
49 as rn
50 from dba_hist_snapshot s
51 where s.snap_id between '||snap_beg||' and '||snap_end||'
52 )
53 select snap_group_type(inst_num, start_snap_id, end_snap_id)
54 from data
55 where end_snap_id is not null and mod(rn,'||n||') = 1
56 order by inst_num, start_snap_id
57 ';
58 sql_str := sql_beg||sql_mid||sql_end;
59 end if;
60 open res for sql_str;
61 loop
62 fetch res into rec;
63 exit when res%notfound;
64 pipe row(rec);
65 end loop;
66 close res;
67 end;
68 /
Function created.
SQL> select * from table(get_snap_groups(53110, 53123, 2))
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53111
1 53112 53113
1 53114 53115
1 53116 53117
1 53118 53119
1 53120 53121
1 53122 53123
7 rows selected.
SQL> select * from table(get_snap_groups(53110, 53123, 3))
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53112
1 53113 53115
1 53116 53118
1 53119 53121
1 53122 53123
5 rows selected.
SQL> select * from table(get_snap_groups(53110, 53123, 4))
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53113
1 53114 53117
1 53118 53121
1 53122 53123
4 rows selected.[Updated on: Wed, 01 March 2023 00:07] Report message to a moderator |
|
|
|
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687358 is a reply to message #687356] |
Wed, 01 March 2023 00:45 |
|
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If I understand you, you don't want each group starting with the next snap but each group starting with the previous end snap, so:
SQL> Create or replace function get_snap_groups
2 (snap_beg integer, snap_end integer, n integer)
3 return snap_group_array pipelined
4 is
5 sql_beg varchar2(256);
6 sql_mid varchar2(1000);
7 sql_end varchar2(1000);
8 sql_str varchar2(2000);
9 res sys_refcursor;
10 rec snap_group_type;
11 begin
12 if n < 2 then
13 raise_application_error (-20001, 'n should be greater than or equal to 2');
14 end if;
15 if n = 2 then
16 sql_str :=
17 'with
18 data as (
19 select s.instance_number as inst_num, s.snap_id as start_snap_id,
20 lead(s.snap_id,1,null) over
21 (partition by s.instance_number order by s.snap_id)
22 as end_snap_id,
23 row_number() over (partition by s.instance_number order by s.snap_id)
24 as rn
25 from dba_hist_snapshot s
26 where s.snap_id between '||snap_beg||' and '||snap_end||'
27 )
28 select snap_group_type(inst_num, start_snap_id, end_snap_id)
29 from data
30 where end_snap_id is not null
31 order by inst_num, start_snap_id';
32 else
33 sql_beg :=
34 'with
35 data as (
36 select s.instance_number as inst_num, s.snap_id as start_snap_id,
37 coalesce(';
38 sql_mid := '';
39 for i in 1..n-1 loop
40 sql_mid := sql_mid ||
41 '
42 lead(s.snap_id,'||to_char(n-i)||',null) over
43 (partition by s.instance_number order by s.snap_id),';
44 end loop;
45 sql_mid := rtrim(sql_mid,',');
46 sql_end :=
47 ') as end_snap_id,
48 row_number() over (partition by s.instance_number order by s.snap_id)
49 as rn
50 from dba_hist_snapshot s
51 where s.snap_id between '||snap_beg||' and '||snap_end||'
52 )
53 select snap_group_type(inst_num, start_snap_id, end_snap_id)
54 from data
55 where end_snap_id is not null
56 and mod(rn,'||to_char(n-1)||') = 1
57 order by inst_num, start_snap_id
58 ';
59 sql_str := sql_beg||sql_mid||sql_end;
60 end if;
61 open res for sql_str;
62 loop
63 fetch res into rec;
64 exit when res%notfound;
65 pipe row(rec);
66 end loop;
67 close res;
68 end;
69 /
Function created.
SQL> select * from table(get_snap_groups(53110, 53123, 2))
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53111
1 53111 53112
1 53112 53113
1 53113 53114
1 53114 53115
1 53115 53116
1 53116 53117
1 53117 53118
1 53118 53119
1 53119 53120
1 53120 53121
1 53121 53122
1 53122 53123
13 rows selected.
SQL> select * from table(get_snap_groups(53110, 53123, 3))
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53112
1 53112 53114
1 53114 53116
1 53116 53118
1 53118 53120
1 53120 53122
1 53122 53123
7 rows selected.
SQL> select * from table(get_snap_groups(53110, 53123, 4))
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53113
1 53113 53116
1 53116 53119
1 53119 53122
1 53122 53123
5 rows selected.[Updated on: Wed, 01 March 2023 00:46] Report message to a moderator |
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687359 is a reply to message #687356] |
Wed, 01 March 2023 02:34 |
|
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
avtaritet,
Did you try the last query that I posted? It provides everything you have asked for and works for all values of n, and I posted the test results to prove it. Here it is again without line numbers and using dba_hist_snapshot, so you can just copy and paste it and run it.
def n = 1
select inst_num, start_snap_id, end_snap_id
from (select instance_number as inst_num, snap_id as start_snap_id,
lead(snap_id) over (partition by instance_number order by snap_id) as end_snap_id
from (select instance_number, snap_id
from (select instance_number, snap_id,
row_number() over (partition by instance_number order by snap_id) rn
from dba_hist_snapshot
where snap_id between 165952 and 165959)
start with rn = 1
connect by prior instance_number = instance_number and prior rn = rn - &n
union
select instance_number, max(snap_id) as snap_id
from dba_hist_snapshot
group by instance_number))
where end_snap_id is not null
order by inst_num, start_snap_id
/
def n = 2
/
def n = 3
/
def n = 4
/
def n = 5
/
def n = 6
/
def n = 7
/
def n = 8
/
|
|
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687365 is a reply to message #687359] |
Wed, 01 March 2023 13:44 |
|
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
Barbara Boehmer wrote on Wed, 01 March 2023 02:34avtaritet,
Did you try the last query that I posted? It provides everything you have asked for and works for all values of n, and I posted the test results to prove it. Here it is again without line numbers and using dba_hist_snapshot, so you can just copy and paste it and run it.
def n = 1
select inst_num, start_snap_id, end_snap_id
from (select instance_number as inst_num, snap_id as start_snap_id,
lead(snap_id) over (partition by instance_number order by snap_id) as end_snap_id
from (select instance_number, snap_id
from (select instance_number, snap_id,
row_number() over (partition by instance_number order by snap_id) rn
from dba_hist_snapshot
where snap_id between 165952 and 165959)
start with rn = 1
connect by prior instance_number = instance_number and prior rn = rn - &n
union
select instance_number, max(snap_id) as snap_id
from dba_hist_snapshot
group by instance_number))
where end_snap_id is not null
order by inst_num, start_snap_id
/
def n = 2
/
def n = 3
/
def n = 4
/
def n = 5
/
def n = 6
/
def n = 7
/
def n = 8
/
whatever you posted above i used it generates some wrong result.
1 165952 165954
1 165954 165956
1 165956 165958
1 165958 166156 ---> wrong generated rows 165959 also does not exists
2 165952 165954
2 165954 165956
2 165956 165958
2 165958 166156 ---> wrong generated rows and 165959 also does not exists
|
|
|
|
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687368 is a reply to message #687367] |
Thu, 02 March 2023 05:47 |
|
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If this has nothing to do with rows in dba_hist_snapshot and you just want number groups between 2 values but taking into account the number of instances then:
SQL> def beg_num=53110
SQL> def end_num=53119
SQL> def n=2
SQL> with
2 instances as (select inst_id from gv$instance),
3 numbers as (
4 select &&beg_num+(&&n-1)*(level-1) start_num,
5 least(&&beg_num+(&&n-1)*(level),&&end_num) end_num
6 from dual
7 connect by level <= trunc((&&end_num-&&beg_num)/(&&n-1))+1
8 )
9 select inst_id, start_num, end_num
10 from instances, numbers
11 where start_num < &&end_num
12 order by inst_id, start_num
13 /
INST_ID START_NUM END_NUM
---------- ---------- ----------
1 53110 53111
1 53111 53112
1 53112 53113
1 53113 53114
1 53114 53115
1 53115 53116
1 53116 53117
1 53117 53118
1 53118 53119
9 rows selected.
SQL> def n=3
SQL> /
INST_ID START_NUM END_NUM
---------- ---------- ----------
1 53110 53112
1 53112 53114
1 53114 53116
1 53116 53118
1 53118 53119
5 rows selected.
SQL> def n=4
SQL> /
INST_ID START_NUM END_NUM
---------- ---------- ----------
1 53110 53113
1 53113 53116
1 53116 53119
3 rows selected. |
|
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687381 is a reply to message #687369] |
Sun, 05 March 2023 20:34 |
 |
mathguy
Messages: 79
Registered: January 2023
|
Member |
|
|
This problem is a nice application of MATCH_RECOGNIZE, available since Oracle 12.1.
The input is a set of rows in two columns; the first is a group identifier (in your case, the instance number) and the second is a non-NULL number, unique within the group (in your case, the snapshot id). The task, for a given integer n > 0, is to order the values in each group, and to "mark" the first, the (n+1)st, the (2n+1)st, ... values (and in all cases the last value as well), and then to present the result in the format you requested.
For example, if in a group the values are 1,4,5,89,91,92,93,94 and n = 3, then you want the first, the fourth, the seventh, and the last (the eighth) values; these are 1, 89, 93 and 94. And then you want the result shown like this:
start end
1 89
89 93
93 94
Correct?
The problem has nothing to do with "instance number" and "snapshot id" - I will treat it in full generality.
Here is how MATCH_RECOGNIZE can solve this problem. I use n = 2 for illustration. Alas for some reason I can't understand, the syntax doesn't allow a bind variable in the relevant place (in the PATTERN clause of MATCH_RECOGNIZE); you can use a substitution variable, as was proposed in other solutions in this thread, but be mindful of potential risks.
I included an extensive test case to verify that the query returns the correct result for all cases.
create table inputs (grp, num) as
select 1, 1000 from dual union all
select 2, 2001 from dual union all
select 2, 2002 from dual union all
select 3, 3100 from dual union all
select 3, 3200 from dual union all
select 3, 3300 from dual union all
select 4, 4111 from dual union all
select 4, 4222 from dual union all
select 4, 4333 from dual union all
select 4, 4444 from dual union all
select 5, 5001 from dual union all
select 5, 5002 from dual union all
select 5, 5003 from dual union all
select 5, 5004 from dual union all
select 5, 5005 from dual union all
select 6, 1111 from dual union all
select 6, 2222 from dual union all
select 6, 3333 from dual union all
select 6, 4444 from dual union all
select 6, 5555 from dual union all
select 6, 6666 from dual
;
select grp, start_num, end_num
from inputs
match_recognize(
partition by grp
order by num
measures min(num) as start_num, nvl(next(num), max(num)) as end_num, match_number() as mn
pattern ( x{1,2} ) -- replace 2 with another hard-coded integer, or substitution variable &n
define x as null is null
)
where start_num != end_num or mn = 1
;
GRP START_NUM END_NUM
---------- ---------- ----------
1 1000 1000
2 2001 2002
3 3100 3300
4 4111 4333
4 4333 4444
5 5001 5003
5 5003 5005
6 1111 3333
6 3333 5555
6 5555 6666
|
|
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687394 is a reply to message #687388] |
Mon, 06 March 2023 08:16 |
|
mathguy
Messages: 79
Registered: January 2023
|
Member |
|
|
Michel Cadot wrote on Mon, 06 March 2023 00:42
Quote:This problem is a nice application of MATCH_RECOGNIZE
Not at all, it is just a simple row generator.
You just make up a new problem that has nothing to do with OP's one.
How so?
I can't read people's minds, and in particular I don't know what you mean by "a simple row generator". (By the way, this is why when I answer a question with a not 100% clear task description, I include all the assumptions I made - I don't expect others to read my mind either.)
I may be mistaken, but it seems to me that your assumption is that for a given instance, the snapshot id's are consecutive integers. Is that what you mean?
If so, perhaps you missed the following clarification from the OP:
Quote:I have non consecutive snap_id, some have been deleted.
Any strictly increasing sequence of integers can be obtained from a sequence of consecutive integers, by deleting some of the elements. So, my problem is exactly what the OP requested. If you feel otherwise, please explain. Or, perhaps, let's let the OP read the answer and tell us if it is what he needed, or not.
|
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687396 is a reply to message #687381] |
Mon, 06 March 2023 08:38 |
|
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
mathguy wrote on Sun, 05 March 2023 20:34This problem is a nice application of MATCH_RECOGNIZE, available since Oracle 12.1.
The input is a set of rows in two columns; the first is a group identifier (in your case, the instance number) and the second is a non-NULL number, unique within the group (in your case, the snapshot id). The task, for a given integer n > 0, is to order the values in each group, and to "mark" the first, the (n+1)st, the (2n+1)st, ... values (and in all cases the last value as well), and then to present the result in the format you requested.
For example, if in a group the values are 1,4,5,89,91,92,93,94 and n = 3, then you want the first, the fourth, the seventh, and the last (the eighth) values; these are 1, 89, 93 and 94. And then you want the result shown like this:
start end
1 89
89 93
93 94
Correct?
The problem has nothing to do with "instance number" and "snapshot id" - I will treat it in full generality.
Here is how MATCH_RECOGNIZE can solve this problem. I use n = 2 for illustration. Alas for some reason I can't understand, the syntax doesn't allow a bind variable in the relevant place (in the PATTERN clause of MATCH_RECOGNIZE); you can use a substitution variable, as was proposed in other solutions in this thread, but be mindful of potential risks.
I included an extensive test case to verify that the query returns the correct result for all cases.
create table inputs (grp, num) as
select 1, 1000 from dual union all
select 2, 2001 from dual union all
select 2, 2002 from dual union all
select 3, 3100 from dual union all
select 3, 3200 from dual union all
select 3, 3300 from dual union all
select 4, 4111 from dual union all
select 4, 4222 from dual union all
select 4, 4333 from dual union all
select 4, 4444 from dual union all
select 5, 5001 from dual union all
select 5, 5002 from dual union all
select 5, 5003 from dual union all
select 5, 5004 from dual union all
select 5, 5005 from dual union all
select 6, 1111 from dual union all
select 6, 2222 from dual union all
select 6, 3333 from dual union all
select 6, 4444 from dual union all
select 6, 5555 from dual union all
select 6, 6666 from dual
;
select grp, start_num, end_num
from inputs
match_recognize(
partition by grp
order by num
measures min(num) as start_num, nvl(next(num), max(num)) as end_num, match_number() as mn
pattern ( x{1,2} ) -- replace 2 with another hard-coded integer, or substitution variable &n
define x as null is null
)
where start_num != end_num or mn = 1
;
GRP START_NUM END_NUM
---------- ---------- ----------
1 1000 1000
2 2001 2002
3 3100 3300
4 4111 4333
4 4333 4444
5 5001 5003
5 5003 5005
6 1111 3333
6 3333 5555
6 5555 6666
Thank you so much mathguy this is so good script i did not heard before. thanks a lot you guys. You are just awesome!!
[Updated on: Mon, 06 March 2023 08:38] Report message to a moderator |
|
|
|
| Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687397 is a reply to message #687394] |
Mon, 06 March 2023 08:38 |
|
Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I think you did not read clearly the whole topic.
I admit I, myself, didn't see that start of a group s end of previous group.
I did answer to OP's first answer to my posts that my snap ids are not consecutive this is why number of ids in groups seem to not be equal and this is why I numbered the rows (row_number...).
Then during the posts it appears (from his complaint about wrong results when there are non consecutive ids in dba_hist_snapshots) that OP does not care about what's inside a table in the database but wants groups of consecutive numbers.
In the end, he clearly said that my last query, with a simple row generator, fits his needs (although I have some doubt about the instance ids but kept them to match the first post).
|
|
|
|
Goto Forum:
Current Time: Mon Oct 02 19:36:03 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
((