Get max of a column in a LOD function using group by-CodePudding

On a combination of product, site, station and date I want the row with the max dist_sn_count_at_blob.

Code:

Select a.product_code,a.site_name,a.station_type,a.line_type,a.dist_sn_count_at_blob,a.date_b,
a.pass_blob,a.fail_blob,
a.id  from insight_info_temp a,
(select insight_info_temp.id,product_code, site_name, station_type, 
 date_b,
 max(dist_sn_count_at_blob)
 from insight_info_temp 
 group by product_code, site_name, station_type,insight_info_temp.id,
 date_b) b 
 where a.product_code = b.product_code
 and a.site_name = b.site_name
 and a.station_type = b.station_type
 and a.date_b = b.date_b
 and a.product_code ='D00' 
 and a.site_name = 'F00'
 and a.station_type='A00'
 and a.id = b.id;

Problem: I am not getting one row per date as desired. I am getting all the rows for all dates, including all the sn counts.

Screenshot of output I got below(on the left there's a single product, single site, and single station filtered out)

I only wanted the highlighted rows with the max dist_sn per date

Update: This worked https://intellipaat.com/community/7651/get-records-with-max-value-for-each-group-of-grouped-sql-results

CodePudding user response：

Maybe you should add a row_number by on the outer query results like below and then select the highest per date.

select 
product_code,
site_name,
station_type,
line_type,
dist_sn_count_at_blob,
date_b,
pass_blob,
fail_blob,
id 
from 
(
Select 
a.product_code,
a.site_name,
a.station_type,
a.line_type,
a.dist_sn_count_at_blob,
a.date_b,
a.pass_blob,
a.fail_blob,
a.id, 
row_number () over (partition by  a.date_b order by a.dist_sn_count_at_blob desc) as rn
from insight_info_temp a,
(select insight_info_temp.id,product_code, site_name, station_type, 
 date_b,
 max(dist_sn_count_at_blob)
 from insight_info_temp 
 group by product_code, site_name, station_type,insight_info_temp.id,
 date_b) b 
 where a.product_code = b.product_code
 and a.site_name = b.site_name
 and a.station_type = b.station_type
 and a.date_b = b.date_b
 and a.product_code ='D00' 
 and a.site_name = 'F00'
 and a.station_type='A00'
 and a.id = b.id
  )t 
where t.rn=1

CodePudding user response：

Select a.product_code,
       a.site_name,
       a.station_type,
       a.line_type,
       a.dist_sn_count_at_blob,
       a.date_b,
       a.pass_blob,
       a.fail_blob,
        a.id
from insight_info_temp a
where dist_sn_count_at_blob =
      (select max(dist_sn_count_at_blob) from insight_info_temp
        group by grouping sets  (product_code,site_name,station_type,date_b) limit 1 )
and a.product_code ='D00'
and a.site_name = 'F00'
and a.station_type='A00';