Oracle sql generate consecutive numbers per row-CodePudding

I have a table with a seqno column. I would like to create another column (grpseqno) based on this which is numbered from 1 to 3 only as shown below. It would be neat if this is created by Oracle sql. Thank you in advance.

seqno  grpseqno
1          1
2          2
3          3
4          1
5          2
6          3
7          1

I cannot seem to get to cycle from 1 to 3

CodePudding user response：

One option might be such a case expression:

Sample data:

SQL> with test (seqno) as
  2    (select 1   level - 1 seqno
  3     from dual
  4     connect by level <= 7
  5    )

Query begins here:

  6  select seqno,
  7    case when mod(seqno, 3) = 0 then 3
  8         else mod(seqno, 3)
  9    end grpseqno
 10  from test
 11  order by seqno;

     SEQNO   GRPSEQNO
---------- ----------
         1          1
         2          2
         3          3
         4          1
         5          2
         6          3
         7          1

7 rows selected.

SQL>