X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 31A37D1C4B9 for ; Tue, 29 Jul 2003 15:13:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 77654-01 for ; Tue, 29 Jul 2003 12:12:10 -0300 (ADT) Received: from connect.no-ip.org (pD9505046.dip.t-dialin.net [217.80.80.70]) by svr1.postgresql.org (Postfix) with ESMTP id 37C45D1C4EE for ; Tue, 29 Jul 2003 12:13:09 -0300 (ADT) Received: from spineofgod (spineofgod.nineinchnet [192.168.2.15]) by connect.no-ip.org (Postfix) with SMTP id EC03B8DCC7; Tue, 29 Jul 2003 17:13:02 +0200 (CEST) Message-ID: <000401c355e4$11b0cdd0$0f02a8c0@spineofgod> From: "Oliver Heinz" To: "Tom Lane" Cc: References: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de> <200307280947.21658.josh@agliodbs.com> <1059478450.3f265bb210e14@stud.fbi.fh-darmstadt.de> <22142.1059490856@sss.pgh.pa.us> Subject: Re: Problem using Subselect results Date: Tue, 29 Jul 2003 17:14:12 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/430 X-Sequence-Number: 14544 I'll try this tomorrow - combining DISTINCT ON (two.two_id) and sorting by two.updatenr could (should) have the desired effect - I never thought about using ORDER and DISTINCT that way. I'll report my success or failure... Thanks so far! Bye, Oliver ----- Original Message ----- From: "Tom Lane" To: Cc: Sent: Tuesday, July 29, 2003 5:00 PM Subject: Re: [SQL] Problem using Subselect results > oheinz@stud.fbi.fh-darmstadt.de writes: > > But as this data is time sensitive, we introduce some kind of time stamp - a > > serial which is global to all tables. Now, for each record in table 'one' i > > want to see only the corresponding records in tables two, three, etc... that > > were created before 'one.updatenr' > > > SELECT * FROM one, two WHERE (one.two_id=two.two_id AND one.updatenr > > > two.updatenr); > > > This might match multiple records in tables two (two_id is not a pk, we have > > historic records in this table). Now I want only the most current version > > before one.updatenr. - And that's where I run into trouble. > > You might be able to make this work by using SELECT DISTINCT ON. See > the "weather reports" example in the SELECT reference page. > > regards, tom lane >