Aggregating on two fields and pulling other fields based on the max

[mozgheib]

Hello I have written a query for Oracle 7. It should
get max date and group by two fields. Now Based on
the max I would like also the query to return the remaining
fields for each row returned meeting that criteria.
The query is below but it is too long. You will notice
the select max part of it is the main logic.

WHat is the optimal way of writing it. Please remember it is oracle 7 you cannot use the partition over function.

See below. Thanks



select distinct c.oriontank, e.disp_tankname, d.orionprop, a.rslt_value, a.rslt_value, 0,
'', 'LMS', sysdate, user, 0 , to_date(to_char(b.smpl_dt, 'dd/mm/yyyy') || ' ' || b.smpl_tm || ':00' , 'dd/mm/yyyy hh24:mi:ss'), 0, 0
from totalplant.ip_tst_rslt a, totalplant.ip_smpl b, ps_lims_pnt_map c, ps_lims_prop_map d, ps_phd_data_map e
where a.smpl_name = b.smpl_name
and b.type = 'S'
and b.smpl_pnt_name||a.prprty_name||b.smpl_dt in

( select a.smpl_pnt_name||b.prprty_name||max(a.smpl_dt)
from totalplant.ip_smpl a, totalplant.ip_tst_rslt b, ps_lims_pnt_map c, ps_lims_prop_map d, ps_phd_data_map e
where
a.smpl_name = b.smpl_name
and a.type = 'S'
and a.smpl_pnt_name = c.smpl_pnt_name
and b.prprty_name = rtrim(d.propname)
and e.oriontank = c.oriontank
and ( a.smpl_dt < sysdate - 1 and a.smpl_dt > sysdate - 7 )
and isnum(b.rslt_value) = 0
group by a.smpl_pnt_name,b.prprty_name
)
and b.smpl_pnt_name = c.smpl_pnt_name
and a.prprty_name = rtrim(d.propname)
and e.oriontank = c.oriontank
and isnum(a.rslt_value) = 0
/

 

[SantaMufasa]

Moz,

You may think that the query is long, but if all of the criteria are necessary to produce the correct results, then who cares? What criteria are you thinking are unnecessary?

Mufasa
(aka Dave of Sandy, Utah, USA @ 04:30 (19May04) UTC (aka "GMT" and "Zulu"), 21:30 (18May04) Mountain Time)

 

[mozgheib]

One clarification. I meant to say my query takes too long
to execute.

I do need all the criteria. I am sure there is a different way to write it. I know in sybase I could have done a simple select statement with my criteria and all the fields that I needed based on the group by max are added to the select right after the max.

I will rephrase my question.
I have tables a and b
I link a and b on a key and group by two fields to get
the max date for those group by. Now I have other fields
I need to include in my select but they do not make part
of the group by order.

So tables a and b. Fields x y z in a and x l m n in b
I need to link table a and b on x based on max(z)
group by y and m. I also need to have n returned in my
query. How do I get n without impacting my group by order and the max.

Thanks.

 

[sem]

Is concatenation really your business requirement?

...
and b.smpl_pnt_name,a.prprty_name,b.smpl_dt in 
( select a.smpl_pnt_name,b.prprty_name,max(a.smpl_dt)
...

Regards, Dima

http://seminihin.narod.ru

 

[mozgheib]

No actually concatenation is not my business requirement.

I tried you suggestion and it does not work. I get an error
message. (Invalid relational operator) it is complaining
about the commas in the and statement that is after
b.smpl_pnt_name

 

[sem]

Oops, missed parenthesis:

...
and (b.smpl_pnt_name,a.prprty_name,b.smpl_dt) in 
( select a.smpl_pnt_name,b.prprty_name,max(a.smpl_dt)
...

Regards, Dima

http://seminihin.narod.ru

 

[mozgheib]

thanks. I was wondering if there is a faster way
for writing it.