sql - 如何使用窗口函数获取最高价格及其 ID？

Question

我有这个问题

select adate, factoryid, purchid, itemname, max(price) as price 
from tableb where catnum = 9
group by adate, factoryid, purchid, itemname
order by adate, factoryid, purchid, itemname

但我想要该行的 ID。所以在一个完美的世界中:

select id, adate, factoryid, purchid, itemname, max(price) as price 
from tableb where catnum = 9
group by adate, factoryid, purchid, itemname
order by adate, factoryid, purchid, itemname

但我知道那是行不通的。

所以我尝试了这个:

select id, adate, factoryid, purchid, itemname, 
       max(price) over(partition by adate, factoryid, purchid, itemname) as price 
from tableb where catnum = 9

那是行不通的。所有 ID 的价格都是重复的。查询结果集从 4000 行增加到 11000 行。

很明显，我以某种方式弄错了窗口函数。首先，我做错了什么，其次，当然，我该如何解决？

Answer 1

SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY adate, factoryid, purchid, itemname ORDER BY price DESC, id DESC) rn
        FROM    tableb 
        WHERE   catnum = 9
        ) q
WHERE   rn = 1