Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename
    (col1 int, col2 int, col3 varchar2(3), col4 varchar2(3))
;
INSERT ALL 
    INTO tablename (col1, col2, col3, col4)
         VALUES (17, 5678, 'abc', 'mno')
    INTO tablename (col1, col2, col3, col4)
         VALUES (16, 5678, 'abc', 'mno')
    INTO tablename (col1, col2, col3, col4)
         VALUES (15, 5678, 'abc', 'mno')
    INTO tablename (col1, col2, col3, col4)
         VALUES (14, 5678, 'abc', 'mno')
    INTO tablename (col1, col2, col3, col4)
         VALUES (13, 5678, 'abc', 'mno')
    INTO tablename (col1, col2, col3, col4)
         VALUES (10, 1234, 'def', 'pqr')
SELECT * FROM dual
;
6 rows affected

select t.*,
  case mod((row_number() over (partition by t.col3, t.col4 order by t.col1, t.col2)), 3) 
    when 1 then 'a'
    else 'b'
  end output
from tablename t 
order by t.col1 desc


COL1
COL2
COL3
COL4
OUTPUT


17
5678
abc
mno
b

16
5678
abc
mno
a

15
5678
abc
mno
b

14
5678
abc
mno
b

13
5678
abc
mno
a

10
1234
def
pqr
a

COL1	COL2	COL3	COL4	OUTPUT
17	5678	abc	mno	b
16	5678	abc	mno	a
15	5678	abc	mno	b
14	5678	abc	mno	b
13	5678	abc	mno	a
10	1234	def	pqr	a