Oracle Identity column with custom grouping column

Question

I am working on a requirement where I need to populate a unique constant identifier on a row which is the manager row and unique in each department. My table structure is

CREATE
  TABLE TEST_ORGANIZATION
  (
    EMPLOYEE_ID   NUMBER NOT NULL,
    MANAGER_ID    NUMBER,
    FIRST_NAME    VARCHAR2(256),
    DEPARTMENT_ID VARCHAR2(28) NOT NULL,
    UUID          VARCHAR2(28) ,
    PRIMARY KEY(UUID)
  );

This table contains information as.

UUID	DEPARTMENT_ID	EMPLOYEE_ID	MANAGER_ID	FIRST_NAME
radmon1	finance	employee1		John B
radmon2	finance	employee2	employee1	Michal
radmon3	finance	employee3	employee1	Ronaldo
radmon4	finance	employee4	employee1	Thomas
radmon5	finance	employee5		Percey
radmon6	account	employee6		Stacy
radmon7	account	employee7		Jordan
radmon8	account	employee8	employee6	Micky
radmon9	account	employee9	employee6	Author
radmon10	account	employee10	employee6	Gordan

I would like to add another column to the table to provide a sequence to managers only (where Manager_ID is null). But, the sequence should be grouped with DEPARTMENT_ID

ALTER TABLE TEST_ORGANIZATION ADD SEQUENCE_ID NUMBER

UUID	DEPARTMENT_ID	EMPLOYEE_ID	MANAGER_ID	FIRST_NAME	SEQUENCE_ID
radmon1	finance	employee1		John B	1
radmon2	finance	employee2	employee1	Michal
radmon3	finance	employee3	employee1	Ronaldo
radmon4	finance	employee4	employee1	Thomas
radmon5	finance	employee5		Percey	2
radmon6	account	employee6		Stacy	1
radmon7	account	employee7		Jordan	2
radmon8	account	employee8	employee6	Micky
radmon9	account	employee9	employee6	Author
radmon10	account	employee10	employee6	Gordan

I tried using sequence and identity columns added after oracle 12/19c.

I could it programmatically from backend service and update SEQUENCE_ID using
Select NVL(MAX(SEQUENCE_ID), 0) + 1 FROM TEST_ORGANIZATION WHERE MANAGER_ID is NULL AND DEPARTMENT_ID = ? query. But, I would like to know if there is any function in Oracle 19c to handle this behaviour on the Database side itself.

Answer 1

Try This:

SELECT d1.*,
       1 AS f,
       CASE WHEN manager_id is null then
          RANK() OVER (partition by department_ID order by manager_id nulls first,employee_id)
       end as sequenc
FROM   (
         SELECT 'radmon1' AS UUID,'finance' AS DEPARTMENT_ID,'employee1' AS EMPLOYEE_ID,'' AS MANAGER_ID,'John B' AS    FIRST_NAME          from dual UNION ALL
         SELECT 'radmon2','finance','employee2','employee1','Michal'     from dual UNION ALL
         SELECT 'radmon3','finance','employee3','employee1','Ronaldo'    from dual UNION ALL
         SELECT 'radmon4','finance','employee4','employee1','Thomas'     from dual UNION ALL
         SELECT 'radmon5','finance','employee5','','Percey'         from dual UNION ALL
         SELECT 'radmon6','account','employee6','','Stacy'              from dual UNION ALL
         SELECT 'radmon7','account','employee7','','Jordan'            from dual UNION ALL
         SELECT 'radmon8','account','employee8','employee6','Micky'      from dual UNION ALL
         SELECT 'radmon9','account','employee9','employee6','Author'     from dual UNION ALL
         SELECT 'radmon10','account','employee10','employee6','Gordan'   from dual
       )d1;