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