Stuck on query with latest price

Transact-SQL
1

Hi there,

I'm using the following query to get the latest price from each product between a certain date. In the table 'prices' every price change of a product is registered as a new row.

select max(date) , product_id, price from prices
where date between '2015-01-01' and '2015-12-31'
group by product_id, price

As a result I get all prices per product but I want one (the latest) price per product. Can anyone please tell me what's wrong in this query.
Thank you very much !

2 
SELECT date, product_id, price
FROM (
    SELECT date, product_id, price,
        ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY date DESC) AS row_num
    FROM dbo.prices
    WHERE date >= '20150101' and  date < '20160101'
) AS derived
WHERE row_num = 1
3

Thank you very much !

4

I have another challenge on this query.
I now get all the right results but i now want the latest price > 0.
So it can be that there is an product which has a price of 4,- on 10-09-2015 but has a price of 0,- on 10-10-2015. So now I get the line with the price of 0,-. But I want the line of 4,-. Hope you get the idea :slight_smile:.

Thank you again.

5

Sorry, I was too fast with asking :slight_smile:
I solved it.