SQL - Limiting number of rows using an aggregate function

[eja5866]

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

Here 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

 

[Thargy]

eja,

For future reference,there's no need to post the same question on more than one forum, as Oracle folks tend to look through the various forums anyway. Anyhow, try

select * from
(
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
) where rownum < 2;

and let us know how you get on.

Regards

T

 

[eja5866]

T -

Thanks! That did the trick!