使用Group by组获取LOD函数中的列的最大值

Question

在产品，站点，站和日期的组合中，我希望使用Max Dist_sn_count_at_blob的行。

代码：

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;

问题：我根据需要获得每个日期一行。我将获得所有日期的所有行，包括所有SN计数。

输出的屏幕截图我在下面（左侧有一个产品，单个站点和单个站点过滤掉）

我只想要每个日期具有最大dist_sn的突出显示的行

更新：这有效 https：> https： //intellipaat.com/community/7651/get-records-with-max-value-for-each-group-group-of-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-for-sql-results

Answer 1

也许您应该添加a

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

Answer 2

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