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 8D7FBD1C4CD for ; Tue, 29 Jul 2003 15:00:58 +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 75991-04 for ; Tue, 29 Jul 2003 11:59:59 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 86174D1C4B9 for ; Tue, 29 Jul 2003 12:00:57 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6TF0uQ3022143; Tue, 29 Jul 2003 11:00:56 -0400 (EDT) To: oheinz@stud.fbi.fh-darmstadt.de Cc: "pgsql-sql@postgresql.org" Subject: Re: Problem using Subselect results In-reply-to: <1059478450.3f265bb210e14@stud.fbi.fh-darmstadt.de> References: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de> <200307280947.21658.josh@agliodbs.com> <1059478450.3f265bb210e14@stud.fbi.fh-darmstadt.de> Comments: In-reply-to oheinz@stud.fbi.fh-darmstadt.de message dated "Tue, 29 Jul 2003 13:34:10 +0200" Date: Tue, 29 Jul 2003 11:00:56 -0400 Message-ID: <22142.1059490856@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/429 X-Sequence-Number: 14543 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