sql - Repeating sequence within a group of data - Stack Overflow

I am having difficulty in writing one SQL query. The need I have is with in a group of rows I have to assign a sequence for each row for which I can simply use rank to write my query. However, I need to also use the lower limit and upper limit of the sequence number and if the upper limit is crossed, I have to start again from lower limit.

I can number each row in the group using either row num or rank. But this above requirement is almost done but for some rows, there is a wrong number assigned.

I will try explaining using an example

ROWNUM	MY_SEQ_5_TO_8
1	5
2	6
3	7
4	8
5	5
6	6
7	7
8	8
9	5
10	6
11	7
12	8
13	5
14	6
15	7

I am having difficulty in writing one SQL query. The need I have is with in a group of rows I have to assign a sequence for each row for which I can simply use rank to write my query. However, I need to also use the lower limit and upper limit of the sequence number and if the upper limit is crossed, I have to start again from lower limit.

I can number each row in the group using either row num or rank. But this above requirement is almost done but for some rows, there is a wrong number assigned.

I will try explaining using an example

ROWNUM	MY_SEQ_5_TO_8
1	5
2	6
3	7
4	8
5	5
6	6
7	7
8	8
9	5
10	6
11	7
12	8
13	5
14	6
15	7

Example query I tried but its not giving the complete correct result. Could anyone provide me some pointers to correct this code?

with mylimits as
 (select 5 lowerlimit, 8 upperlimit from dual),
mydata as
 (SELECT Rownum A FROM Dual CONNECT BY Rownum <= 15)
select m.a,
       case
         when m.a = mod(m.a, l.upperlimit - l.lowerlimit + 2) then
          m.a + l.lowerlimit - 1
         when m.a <> mod(m.a, l.upperlimit - l.lowerlimit + 2) then
          mod(m.a, l.upperlimit - l.lowerlimit + 2) + l.lowerlimit
       end rep_seq
  from mydata m, mylimits l

• sql
• oracle-database

Share Improve this question Follow edited Jan 2 at 20:03 Dale K 27.6k1515 gold badges5858 silver badges8383 bronze badges asked Jan 2 at 16:56 VijayVijay 67.4k9494 gold badges237237 silver badges327327 bronze badges 0

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

I think you can define the limit and calculate the position within the cycle using mod(m.a - 1, l.upperlimit - l.lowerlimit + 1) and then adjust again to start from the lower limit.

Example fiddle

  WITH mylimits AS (
    SELECT 5 AS lowerlimit, 8 AS upperlimit FROM dual
),
mydata AS (
    SELECT ROWNUM AS a FROM dual CONNECT BY ROWNUM <= 15
)
SELECT m.a row_num,
       mod(m.a - 1, l.upperlimit - l.lowerlimit + 1) + l.lowerlimit AS rep_seq_5_to_8
FROM mydata m, mylimits l
ORDER BY m.a;

Output

ROW_NUM	REP_SEQ_5_TO_8
1	5
2	6
3	7
4	8
5	5
6	6
7	7
8	8
9	5
10	6
11	7
12	8
13	5
14	6
15	7

One option is to cross join row numbers with generated rows from 5 to 8 and use mod function in where clause to filter rows.

WITH 
   tbl AS 
     ( SELECT ROWNUM AS MY_RN 
       FROM dual 
       CONNECT BY ROWNUM <= 15
     ),
   seq_5_to_8 AS
     ( Select  SEQ, Min(SEQ) Over() - 1 as DIFF 
       From  ( SELECT ROWNUM AS SEQ FROM dual CONNECT BY ROWNUM <= 8 ) 
       Where   SEQ > 4
     )
SELECT      t.MY_RN, s.SEQ
FROM        tbl t
CROSS JOIN  seq_5_to_8 s
WHERE       Mod(t.MY_RN - s.SEQ, s.DIFF) = 0 
ORDER BY    t.MY_RN

MY_RN	SEQ
1	5
2	6
3	7
4	8
5	5
6	6
7	7
8	8
9	5
10	6
11	7
12	8
13	5
14	6
15	7

fiddle