Max & Group，通过在Excel和Workbench中返回不同的结果

Question

编写查询将返回每天每个项目的最新更新记录，换句话说，项目444222每天获得12条记录，查询将提取更新最多的记录。有时会在前一天、第二天和第三天插入记录。当查询在工作台中运行时，我会得到正确的值，但在ExcelPowerQuery中，它会返回最早的记录。是我有编码错误，还是其他原因？如有任何帮助，我们将不胜感激。record_type:varchar，date:date，item_number:int，cycle_id:int，scheduled_cap:int，insert_date:timestamp。

查询附在下面。

Select record_type, date, item_number, cycle_id, scheduled_cap, max(insert_date) 
from db.all_cycles
Where date >= '2016-01-01'
AND record_tyoe = 'I' 
AND cycle_id NOT IN (40, 38, 39, 41, 53, 185)
AND item_number IN ('94850', '94858', '452917')
group by item_number, date, record_type
order by date desc;

Answer 1

您应该使用过滤而不是聚合：

select ac.*
from db.all_cycles ac
where ac.date >= '2016-01-01' and
ac.record_tyoe = 'I' and
ac.cycle_id not in (40, 38, 39, 41, 53, 185) and
ac.item_number in ('94850', '94858', '452917') and
ac.insert_date = (select max(ac2.insert_date)
from db.all_cycles ac2
where ac2.date = ac.date and
ac2.item_number = ac.item_number and
ac2.record_tyoe = ac.record_type
)
order by date desc;