How do I use window function to get max price and its id?

Question

I have this query

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

But I want the id for that row. So in a perfect world:

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

But I know that won't work.

So I tried this:

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

That doesn't work. The price is duplicated for all the ids. And the query result set goes from 4000 rows to 11000.

So obviously, I got the window function wrong somehow. First what did I do wrong and secondly, of course, how do I fix it?

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