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:[EMAIL PROTECTED] |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 |[EMAIL PROTECTED]) | ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
