X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2D22752896 for ; Sat, 11 Jun 2005 00:35:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37862-10 for ; Sat, 11 Jun 2005 03:35:47 +0000 (GMT) Received: from priv-edtnes51.telusplanet.net (outbound04.telus.net [199.185.220.223]) by svr1.postgresql.org (Postfix) with ESMTP id 911F15288D for ; Sat, 11 Jun 2005 00:35:45 -0300 (ADT) Received: from localhost ([199.185.220.240]) by priv-edtnes51.telusplanet.net (InterMail vM.6.01.04.04 201-2131-118-104-20050224) with ESMTP id <20050611033544.YNKE26712.priv-edtnes51.telusplanet.net@localhost> for ; Fri, 10 Jun 2005 21:35:44 -0600 Received: from 64.180.246.130 ( [64.180.246.130]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Fri, 10 Jun 2005 20:35:44 -0700 Message-ID: <1118460944.42aa5c1012c59@webmail.telus.net> Date: Fri, 10 Jun 2005 20:35:44 -0700 From: Mischa Sandberg To: pgsql-sql@postgresql.org Subject: Re: How do write a query... References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 64.180.246.130 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.059 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200506/153 X-Sequence-Number: 21754 > |From: Alain Reymond [mailto:arwebmail@skynet.be] > |Sent: Mittwoch, 01. Juni 2005 18:01 > |Subject: [SQL] How do write a query... > | > |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. Not sure if this covers all that you want, given your examples (what if there's only one row for a given Num value?), but ... select Num, AValue-AValue1 from Tafel join (select Num, min(AValue) as AValue1 from Tafel group by Num) as T using(Num) where AValue > AValue1