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
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';