You could have a look at the OFFSET and LIMIT modifiers
as for untested example
select ((select max( "AValue") from table group by "Num") - "AValue") as
difference from table order by "AValue" desc offset 1
this says: give me a inversed ordered AValue-list but ommitting the first
(biggest) and subtract each from the biggest value of each group
|-----Original Message-----
|From: Alain Reymond [mailto:arwebmail@skynet.be]
|Sent: Mittwoch, 01. Juni 2005 18:01
|To: pgsql-sql@postgresql.org
|Subject: [SQL] How do write a query...
|
|
|Hello,
|
|I have the following problem :
|
|I have a table like
|Id Num Date AValue
|1 10 01/01/2005 50
|2 10 31/05/2005 60
|3 25 02/02/2005 55
|4 25 15/03/2005 43
|5 25 28/05/2005 62
|etc..
|
|Id is unique, Num is an identification number with duplicates possible,
|date is a ... date and Avalue... a value!
|
|If we have
|Id Num Date AValue
|Id1 Num1 Date1 AValue1
|Id2 Num1 Date2 AValue2
|
|The table is ordered on Num+Date.
|What I would like to calculate is (AValue2-AValue1) for a given Num
|(here num1).
|
|In this case, I would have to calculate
|60-50 for Num 10
|and
|43-55, 62-43 for Num 25.
|
|Do you have any idea if it can be done simply with a request...
|
|I thank you
|
|Regards.
|
|Alain Reymond
|
|
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 2: you can get off all lists at once with the unregister command
| (send "unregister YourEmailAddressHere" to
|majordomo@postgresql.org)
|