Pull back values from field B where the data in field A is the max

Question

I have a report in Oracle SQL that brings back a list of all the times a plot of grass has been cut in the current financial year.

This gives me the following information:

Central Asset ID         Grass Cut?        Grass Cut Reason          Cut Date


1234                     Yes              Yes - Grass Cut            14/04/2023


1234                     No               No - Ground waterlogged    28/4/2023

What I would like to do is bring back the central asset id field and the grass cut reason field where the cut date is the latest - in the example above I would like to bring back

Central Asset ID 1234
Grass Cut Reason "No - Ground waterlogged"

into a report that shows the most recent grass cut reason for each plot.

This is my existing code

select
feature.central_asset_id,
case
when observe_parm_opt.obs_parm_opt_name like 'Yes%' then 'Cut' 
when observe_parm_opt.obs_parm_opt_name like 'No%' then 'Not Cut'   
when observe_parm_opt.obs_parm_opt_name is NULL  then '' 
else ''     
end as grasscut,
Max(observe_parm_opt.obs_parm_opt_name) as Grass_Cut_Reason,
max(inspection_feature.feature_insp_date) as Insp_Date

from
feature
inner join inspection_feature on inspection_feature.site_code = feature.site_code and inspection_feature.plot_number = feature.plot_number
inner join insp_condition on inspection_feature.insp_batch_no = insp_condition.insp_batch_no and
inspection_feature.site_code = insp_condition.site_code and inspection_feature.plot_number = insp_condition.plot_number

inner join observe_type on insp_condition.observe_type_key = observe_type.observe_type_key
inner join observe_parameter on observe_type.obs_parm_code = observe_parameter.obs_parm_code
inner join observe_parm_opt on observe_parameter.obs_parm_code = observe_parm_opt.obs_parm_code
inner join central_site on inspection_feature.site_code = central_site.site_code
inner join feature_type on feature.feature_type_code = feature_type.feature_type_code

where
insp_condition.grade_code = observe_parm_opt.obs_parm_opt_code and observe_type.observe_type_code = 'G003' and
inspection_feature.feature_insp_date >= TO_DATE('30/03/2022','DD/MM/YYYY') and
feature.feature_type_code in ('G005')

group by
feature.central_asset_id,
observe_parm_opt.obs_parm_opt_name

score 1 · Accepted Answer · answered Jul 05 '23 at 10:29

Instead of using MAX function, try with ROW_NUMBER analytic one which "sorts" data per each central_asset_id by feature_insp_date in descending order so that the most recent rows are placed on top of the list, i.e. their rn column equals 1.

Your current query (with such an addition, and without group by clause as you don't need it any more) is then a CTE (or a subquery; doesn't really matter) and you'd finally fetch rows that ranked as the highest.

Something like this:

with temp as(
   select
   feature.central_asset_id,
   case
   when observe_parm_opt.obs_parm_opt_name like 'Yes%' then 'Cut' 
   when observe_parm_opt.obs_parm_opt_name like 'No%' then 'Not Cut'   
   when observe_parm_opt.obs_parm_opt_name is NULL  then '' 
   else ''     
   end as grasscut,
   observe_parm_opt.obs_parm_opt_name as Grass_Cut_Reason,
   inspection_feature.feature_insp_date as Insp_Date,
   --
   row_number() over (partition by feature.central_asset_id order by inspection_feature.feature_insp_date desc) rn
   from
   feature
   inner join inspection_feature on inspection_feature.site_code = feature.site_code and inspection_feature.plot_number = feature.plot_number
   inner join insp_condition on inspection_feature.insp_batch_no = insp_condition.insp_batch_no and
   inspection_feature.site_code = insp_condition.site_code and inspection_feature.plot_number = insp_condition.plot_number

   inner join observe_type on insp_condition.observe_type_key = observe_type.observe_type_key
   inner join observe_parameter on observe_type.obs_parm_code = observe_parameter.obs_parm_code
   inner join observe_parm_opt on observe_parameter.obs_parm_code = observe_parm_opt.obs_parm_code
   inner join central_site on inspection_feature.site_code = central_site.site_code
   inner join feature_type on feature.feature_type_code = feature_type.feature_type_code
   --
   where
   insp_condition.grade_code = observe_parm_opt.obs_parm_opt_code and observe_type.observe_type_code = 'G003' and
   inspection_feature.feature_insp_date >= TO_DATE('30/03/2022','DD/MM/YYYY') and
   feature.feature_type_code in ('G005')
)
select central_asset_id, grasscut, grass_cut_reason, insp_date
from temp
where rn = 1

Pull back values from field B where the data in field A is the max

1 Answers1