oracle11g - Use Oracle Partition and Over By clause to retrieve section numbers -

oracle11g - Use Oracle Partition and Over By clause to retrieve section numbers -

my table comprises 4 columns (patient, sample, analysis , component). trying write query @ combination of patient, analysis , component each record , assign "section number". numbering should re-start every patient.

see expected output below. patient 1010 has 3 samples have same analysis-component. hence have same section (1). now, counting restarts patient 2020. patient has 2 samples both have different analysis-component combination. hence placed in separate sections 1 , 2.

patient sample analysis component section number _______ ______ ________ _________ ______________ 1010 720000140249 calc calcium 1 1010 720000140288 calc calcium 1 1010 720000140288 calc calcium 1 2020 720000190504 alb albumin 1 2020 720000160504 alb albumin pct 2 3030 720000134568 calc calcium 1 3030 720000123404 alb albumin 2 3030 720000160765 alb albumin pct 3

i have written next query groups samples same component 1 section. not consider patient or analysis @ all.

your help much appreciated (as always!)

select x.patient, x.sample_number, x.analysis, x.component a.myrowcount x_prev_pat_results x inner bring together ( select distinct x1.component , row_number() on (order x1.component) myrowcount x_prev_pat_results x1 grouping x1.patient ) on x.component = a.component order a.myrowcount, x.patient;

select x.patient, x.sample_number, x.analysis, x.component, dense_rank() over(partition x.patient order x.analysis, x.component) x_prev_pat_results x exists (select 1 x_prev_pat_results x1 x1.component = x.component);

oracle11g