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 !
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
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 .
Thank you again.
Sorry, I was too fast with asking
I solved it.