I have two tables, one that stores the current price, and one that stores the historical price of items. I want to create a query that pulls the current price, and the difference between the current price and the most recent historical price.
In the historical table, I have the start and end times of the price, so I can just select the most recent price, but how do I pull it all together in one query? Or do I have to do a subquery?
from price p
inner join price_history h
on p.id = h.id
This obviously doesn't work, but that is what I'm trying to accomplish.
This gives me the current and historical price. But I want to make sure I have the most RECENT price. How would I do this?
I would do it like this. Note, you may get duplicate records if there are two price entries with the same date for the same
select p.current_price, h.historical_price, p.current_price - h.historical_price as PriceDeff, h.historical_time from price p inner join ( select id, max(historical_time) as MaxHistoricalTime from price_history group by id ) hm on p.id = hm.id inner join price_history h on hm.id = h.id and hm.MaxHistoricalTime = h.historical_time
I don't believe there's a way of doing this without a subquery that isn't worse. On the other hand, if your table is indexed correctly, subqueries returning results of aggregate functions are generally pretty fast.
select p.current_price, h3.historical_price, h3.historical_time from price p, ( select h1.id, max( h1.historical_time ) as MaxHT from price_history h1 group by 1 ) h2, price_history h3 where p.id = h2.id and p.id = h3.id and h2.MaxHT = h3.historical_time