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?

id is not uniquely determined. Do you want all ids or just any one? — Fruitbat, Jul 01 '15 at 21:56

score 3 · Accepted Answer · answered Jul 01 '15 at 21:29

3

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

answered Jul 01 '15 at 21:29

Quassnoi

413,100
91
616
614

score 3 · Answer 2 · answered Jul 01 '15 at 22:31

You can use windowed version of MAX along with FIRST_VALUE, available from SQL Server 2012+:

SELECT DISTINCT adate, factoryid, purchid, itemname, 
       MAX(price) OVER (PARTITION BY adate, factoryid, purchid, itemname) AS price,
       FIRST_VALUE(id) OVER (PARTITION BY adate, factoryid, purchid, itemname
                             ORDER BY price DESC) AS id
FROM tableb 
WHERE catnum = 9
ORDER BY adate, factoryid, purchid, itemname

score 1 · Answer 3 · answered Jul 01 '15 at 22:11

I'm not clear exactly why you want the id but... assuming

you want all ids for the condition
id is a PK of tableb

then this might work:

SELECT tableb.* FROM tableb
INNER JOIN
(
select adate, factoryid, purchid, itemname, max(price) as price 
from tableb where catnum = 9
group by adate, factoryid, purchid, itemname
) AS grouped
ON  tableb.adate = grouped.adate AND
    tableb.factoryid = grouped.factoryid AND
    tableb.purchid = grouped.purchid AND
    tableb.itemname = grouped.itemname AND
    tableb.price = grouped.price
order by tableb.adate, tableb.factoryid, tableb.purchid, tableb.itemname

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

3 Answers3