Oracle SQL - Select only few elements from group -

i have following table representing tasks in processes:

task_id | process_id | task_type_id ========+============+============= 1000    | 1          | 1001    | 1          | b 1002    | 1          | c 1003    | 1          | d 1004    | 2          | 1005    | 2          | c 1006    | 2          | d 1007    | 3          | 1008    | 3          | c 1009    | 3          | d 

i want isolate different process types. process type defined unique sequence of tasks.

the following query

select process_id,        count(*) tasks_no,        listagg(task_type_id,'>') within group (order task_id) task_sequence mytable group process_id 

can isolate task sequences:

process_id | tasks_no | task_sequence ===========+==========+============== 1          | 4        | a>b>c>d 2          | 3        | a>c>d 3          | 3        | a>c>d 

now want aggregate result:

task_sequence | tasks_no | process_no | proc_rep_ids ==============+==========+============+============= a>b>c>d       | 4        | 1          | 1 a>c>d         | 3        | 2          | 2,3 

process_no column should give number of processes having same task sequence. additionally each distinct tasks sequence (process type) in proc_rep_ids column max 3 (representative) process_ids should listed. in case there thousands of processes having same task sequence, 3 process_ids should listed here.

using first function ordering criteria places 3 items in first position should able achieve result.

see sample query used wm_concat because listagg not compatible first.

with mytable (         select 1000 task_id, 1 process_id, 'a' task_type_id         dual         union         select 1001 task_id, 1 process_id, 'b' task_type_id         dual         union         select 1002 task_id, 1 process_id, 'c' task_type_id         dual         union         select 1003 task_id, 1 process_id, 'd' task_type_id         dual         union         select 1004 task_id, 2 process_id, 'a' task_type_id         dual         union         select 1005 task_id, 2 process_id, 'c' task_type_id         dual         union         select 1006 task_id, 2 process_id, 'd' task_type_id         dual         union         select 1007 task_id, 3 process_id, 'a' task_type_id         dual         union         select 1008 task_id, 3 process_id, 'c' task_type_id         dual         union         select 1009 task_id, 3 process_id, 'd' task_type_id         dual          union         select 1010 task_id, 4 process_id, 'a' task_type_id         dual         union         select 1011 task_id, 4 process_id, 'c' task_type_id         dual         union         select 1012 task_id, 4 process_id, 'd' task_type_id         dual          union         select 1013 task_id, 5 process_id, 'a' task_type_id         dual         union         select 1014 task_id, 5 process_id, 'c' task_type_id         dual         union         select 1015 task_id, 5 process_id, 'd' task_type_id         dual           union         select 1016 task_id, 6 process_id, 'a' task_type_id         dual         union         select 1017 task_id, 6 process_id, 'c' task_type_id         dual         union         select 1018 task_id, 6 process_id, 'd' task_type_id         dual      ) select task_sequence, max(tasks_no) tasks_no, count(*) process_no,     listagg(process_id, ',') within group (order process_id) proc_rep_ids,     to_char(wm_concat(process_id) keep (dense_rank first order trunc((seqnum-1)/3))) proc_rep_ids_limited (         select p.*,             row_number() on (partition task_sequence order process_id) seqnum         (                 select process_id,                     count(*) tasks_no,                     listagg(task_type_id, '>') within group (order task_id) task_sequence                 mytable                 group process_id             ) p     ) p group task_sequence