Group by aggragate issue 2

[jimmythegeek]

I have done many group bys, and I think this is doable, but the depth is messing me up.

2 Tables:


RUN
---
SAMP_SITE_ID

RESULT
------
CON_ID
VALUE_RPTD
DEPTH

I want to find the maximum value reported for each sampling site/constituent combination. No problem when I group by the samp_site_id and con_id and do a max for the value_rptd. But I want the depth that belongs to that maximum value_rptd. I can't add the depth to the group by query because there are many depths and I will no longer be able to get the single maximum result for a samp_site_id/con_id combo. Hope this makes sense, and I am sure that others out there have had similar issues.

Thanks in advance for any help.



Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

Select con_id,DEPTH,VALUE_RPTD
from RESULT
inner join
(
Select con_id,DEPTH,max(VALUE_RPTD)
from RESULT
group by con_id,DEPTH)dt
on RESULT.con_id=dt.con_id
and RESULT.VALUE_RPTD=dt.VALUE_RPTD

 

[r937]

SELECT con_id
     , depth
     , value_rptd
  FROM result
INNER 
  JOIN ( SELECT con_id
              , MAX(value_rptd) AS max_depth
           FROM result
         GROUP 
             BY con_id ) AS dt
    ON dt.con_id = result.con_id
   AND dt.max_depth = result.value_rptd

r937.com | rudy.ca

 

[jimmythegeek]

Thank you both, however my result set needs to include the samp_site_id from the RUN table. So based on your sql, here here is my attempt, but I get a syntax error.


SELECT
run.samp_site_id,
res.con_id,
res.value_rptd,
res.depth
FROM
GPL_ADM_GPL_RUN as run
INNER JOIN
GPL_ADM_GPL_RESULT as res ON
run.RUN_NUM = res.RUN_NUM
INNER JOIN (
SELECT
ru.samp_site_id,
re.con_id,
max(re.value_rptd) as maxVal
FROM
GPL_ADM_GPL_RUN as ru
INNER JOIN
GPL_ADM_GPL_RESULT as re ON
ru.RUN_NUM = re.RUN_NUM
GROUP BY ru.samp_site_id, re.con_id ) AS dt
ON dt.samp_site_id = run.samp_site_id AND
dt.con_id = res.con_id AND
dt.maxVal = res.value_rptd


I have verified that the inner select statement IS actually returning the correct values. I am just not sure I got all the syntax right on both joins since there is a sub select.


Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[PHV]

SELECT run.samp_site_id, res.con_id, res.value_rptd, res.depth
FROM [!]([/!]GPL_ADM_GPL_RUN AS run
INNER JOIN GPL_ADM_GPL_RESULT as res ON run.RUN_NUM = res.RUN_NUM[!])[/!]
INNER JOIN (
SELECT ru.samp_site_id, re.con_id, Max(re.value_rptd) AS maxVal
FROM GPL_ADM_GPL_RUN AS ru
INNER JOIN GPL_ADM_GPL_RESULT AS re ON ru.RUN_NUM = re.RUN_NUM
GROUP BY ru.samp_site_id, re.con_id
) AS dt ON dt.samp_site_id = run.samp_site_id AND dt.con_id = res.con_id AND dt.maxVal = res.value_rptd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jimmythegeek]

Thanks, that was it. I appreciate it.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?