获取各inventoryno最新日期最高avgcost的SQL查询求助

需求 #

编写SQL查询，从invtrans表中获取每个唯一inventoryno在最新日期下的最高avgcost，查询条件为DTS < '01-JAN-23'。

当前查询及输出 #

当前执行的查询：

select inventoryno, avgcost, dts
  from invtrans
 where DTS < '01-JAN-23'
 order by dts desc;

查询输出：

尝试的错误查询及问题 #

尝试的查询语句：

Select distinct inventoryno, Max(avgcost), max(dts)
 from invtrans
 where DTS < '01-JAN-23'
 order by inventoryno;

报错信息：not a single-group group function

问题原因：使用聚合函数MAX()时，未通过GROUP BY子句对inventoryno进行分组，同时DISTINCT与聚合函数的用法冲突，不符合SQL的分组规则。

正确解决方案 #

这里提供两种可行的查询方法：

方法1：使用窗口函数（推荐） #

利用RANK()窗口函数，先按inventoryno分组，再按日期降序、成本降序排序，取每组第一行：

SELECT inventoryno, avgcost, dts
FROM (
    SELECT 
        inventoryno, 
        avgcost, 
        dts,
        RANK() OVER (PARTITION BY inventoryno ORDER BY dts DESC, avgcost DESC) AS rn
    FROM invtrans
    WHERE DTS < '01-JAN-23'
) t
WHERE rn = 1;

方法2：子查询关联+分组 #

先查询每个inventoryno的最新日期，再关联原表获取该日期下的最高成本：

SELECT 
    i.inventoryno, 
    MAX(i.avgcost) AS avgcost, 
    i.dts
FROM invtrans i
JOIN (
    SELECT inventoryno, MAX(dts) AS max_dts
    FROM invtrans
    WHERE DTS < '01-JAN-23'
    GROUP BY inventoryno
) m ON i.inventoryno = m.inventoryno AND i.dts = m.max_dts
WHERE i.DTS < '01-JAN-23'
GROUP BY i.inventoryno, i.dts;

期望输出 #

内容的提问来源于stack exchange，提问作者crdbrdbox