SQL - Limiting result sets for aggregate functions

[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

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

 

[ianoctdec]

Not the most elegant solution, but it doesn't need analytical functions:

select b.id_number, b.max_areasum, c.job_number, c.run_number
from
(select a.id_number, max(AREASUM) as max_areasum
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)a) b,
(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)c
where
b.number_id = c.number_id
and
b.max_areasum = c.areasum

Dana

 

[carp]

Have you tried something like

SELECT * FROM 
(
select 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) AS v
WHERE rownum = 1;

?

 

[eja5866]

Yes Thanks! - The Select * from (......
option worked best for when I had just one id_number.


But what if I have a whole set of ID numbers coming from the same table (where id_number whould be constantly changing)?

Right now I have a cursor set up, but I'm experiencing problems. Perhaps a correlated query?

 

[carp]

I'm a bit confused by your question.

Your requirement said "I'm only interested in the first result where the aggregate (sum) is greatest."

So why would id_number be constantly changing? For any given execution, the first row with the greatest sum(area_percent) will always be the first row, regardless of the id_number. The only possible problem I can see with this approach is the situation where two or more rows have the same value for AREASUM. In this case, you need to define what you mean by "first result".

Also, the way your query is set up, you will always have just one ID number. If you need to run a group of ID numbers, you could change
" and id_number = 'X' "
to
" and id_number IN ('X','Y','Z') "
Is this what you are asking about?

 

[eja5866]

You are correct, my original question only involved querying one id_number. Where the aggregate of a number of runs for that id_number would result, and I'd only be interested in the highest AREASUM.

But I really wanted to apply that query to a group of Id_numbers where id_number = v_id. And v_id is being populated by a cursor.
This is the cursor I'm using:
CURSOR c_get_id IS

select distinct id
from AIM.ipc_peaks
where ref_unit is not null
and peak_name is not null
and col_type = 'O'
and job_type = 'IPC';

And then v_id := c_get_id.id


I'm hoping instead to not use a cursor and have it like your example where:
" and id_number IN ('X','Y','Z') "

Sorry for the lack of foresight

 

[carp]

Aha! In that case, yes, you might want to consider a correlated subquery (although I'm sure somebody here has a better way to do it).

 

[carp]

If you don't care about job_number or run_number, you could avoid the correlated query with

SELECT b.id_number, b.max_area
FROM
( select distinct id
    from AIM.ipc_peaks
    where ref_unit is not null
    and peak_name is not null
    and col_type = 'O'
    and job_type = 'IPC') A, 
(SELECT id_number, MAX(areasum) FROM
   (select 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'
group by id_number ,job_number, run_number) v
group by id_number
) B
WHERE a.id = b.id_number;

Note: I haven't tested this.

 

[heppme]

select id_number, job_number, run_number, max_areasum areasum
from
(
select id_number, job_number,run_number, sum(area_percent) AS AREASUM,
max(sum(area_percent)) over (partition by id_number) as max_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
) x
where 1=1
and areasum = max_areasum
order by max_AREASUM DESC