Hello All,
I have a query aggregating a result set and grouping by 3 particular identifiers. I'm only interested in the first result where the aggregate (sum) is greatest. I cannot use rownum since I am using aggregate functions grouping about 50 rows per set. Does anyone know how to limit this rseult set to the top result only?
select distinct id_number, job_number,run_number, sum(area_percent) AS AREASUM
from ipc_peaks
where ref_unit is not null
and peak_name is not null
and col_type = 'O'
and job_type = 'IPC'
and id_number = 'X'
group by id_number ,job_number, run_number
order by AREASUM DESC
SQL>
00020518 BC00456 2 985.72
00020518 RD020518 2 985.65
Heres is an example result set - I only want the first set.
Please help = the more I read, the more it looks like analytic functions, but there has to be an easier way?
thx
I have a query aggregating a result set and grouping by 3 particular identifiers. I'm only interested in the first result where the aggregate (sum) is greatest. I cannot use rownum since I am using aggregate functions grouping about 50 rows per set. Does anyone know how to limit this rseult set to the top result only?
select distinct id_number, job_number,run_number, sum(area_percent) AS AREASUM
from ipc_peaks
where ref_unit is not null
and peak_name is not null
and col_type = 'O'
and job_type = 'IPC'
and id_number = 'X'
group by id_number ,job_number, run_number
order by AREASUM DESC
SQL>
00020518 BC00456 2 985.72
00020518 RD020518 2 985.65
Heres is an example result set - I only want the first set.
Please help = the more I read, the more it looks like analytic functions, but there has to be an easier way?
thx