On Monday 21 May 2012, Jan Bakuwel wrote:
> Hi,
>
> I'm trying to get my head around the following question. As an
> example take a table with products:
>
> productid (pk)
> name
>
> and productprice
>
> productpriceid (pk)
> productid (fk)
> pricedate
> price
>
> There are multiple records in productprice for each product as prices
> (often) go up and (less often) go down.
>
> I'm looking for a query that returns the following:
>
> productid, name, pricedate, current_price, difference
>
> current_price is the latest (ie. most recent date) price of the
> product and difference is the difference in price between the latest
> price and the price before the latest.
>
> Any suggestions how to do this with SQL only? I can make it work with
> a function (probably less efficient) but think this should be
> possible with SQL too...
Something like this ought to do it (not tested):
select latest.price, latest.price - next.price
from (select price from productprice where productid = 1 order by pricedate desc limit 1) latest, (select price from
productpricewhere productid = 1 order by pricedate desc limit 2 offset 1) next;
Regards,
-- Raj
--
Raj Mathur || raju@kandalaya.org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F