From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: master/detail
Date: Mon, 21 May 2012 08:40:26 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
References: <4FB7EEC6.1030004@gmx.net> <4FB8B489.3050701@gmx.net>
	<4FB97B70.6080604@greenpeace.org>
In-Reply-To: <4FB97B70.6080604@greenpeace.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201205210840.26777.raju@linux-delhi.org>

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 productprice where 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