Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 08CCE47653D for ; Fri, 1 Nov 2002 17:09:11 -0500 (EST) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 4BBE747629A for ; Fri, 1 Nov 2002 17:09:10 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.5.9) with ESMTP id 1808845; Fri, 01 Nov 2002 14:11:28 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Peter T. Brown" , pgsql-sql@postgresql.org Subject: Re: making queries more effecient Date: Fri, 1 Nov 2002 14:09:01 -0800 X-Mailer: KMail [version 1.4] References: <1036189492.2013.16.camel@localhost.localdomain> In-Reply-To: <1036189492.2013.16.camel@localhost.localdomain> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200211011409.01771.josh@agliodbs.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/24 X-Sequence-Number: 10166 Peter, For the rest of our sakes, in the future, please format your SQL before=20 posting it to request help.=20 > CREATE TEMP TABLE temp20561149207391 AS SELECT DISTINCT ON ("VisitorID") > "VisitorID","Type" FROM "ProgramEvent" WHERE "ProgramID" =3D 10 ORDER BY > "VisitorID","Created" DESC;INSERT INTO "VisitorPointer839" ("VisitorID") > SELECT temp20561149207391."VisitorID" FROM temp20561149207391 WHERE > temp20561149207391."Type" =3D 0 Can be re-written as: INSERT INTO "VisitorPointer839" ("VisitorID") SELECT "VisitorID" FROM ( SELECT DISTINCT ON ("VisitorID") "VisitorID","Type"=20 FROM "ProgramEvent" WHERE "ProgramID" =3D 10=20 ORDER BY "VisitorID","Created" DESC ) v_first WHERE v_first."Type" =3D 0; Please also keep in mind that the SELECT DISTINCT ON syntax is a=20 PostgreSQL-specific extension and is not portable. --=20 -Josh Berkus Aglio Database Solutions San Francisco