Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0C2B6475C26
	for <pgsql-sql@postgresql.org>; Fri,  1 Nov 2002 17:36:41 -0500 (EST)
Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250])
	by postgresql.org (Postfix) with ESMTP id 6A1BD475AEC
	for <pgsql-sql@postgresql.org>; Fri,  1 Nov 2002 17:36:40 -0500 (EST)
Received: from [66.219.92.2] (HELO chocolate-mousse)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.5.9)
	with ESMTP id 1808900; Fri, 01 Nov 2002 14:38:59 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Chad Thompson" <chad@weblinkservices.com>
Subject: Re: making queries more effecient
Date: Fri, 1 Nov 2002 14:36:32 -0800
X-Mailer: KMail [version 1.4]
References: <1036189492.2013.16.camel@localhost.localdomain>
	<200211011409.01771.josh@agliodbs.com>
	<038c01c281f5$fcae7ee0$32021aac@chad>
In-Reply-To: <038c01c281f5$fcae7ee0$32021aac@chad>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211011436.32575.josh@agliodbs.com>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/27
X-Sequence-Number: 10169


Chad,

> Im not sure I can get my head around the difference between doing your
> subselect....
>=20
> INSERT INTO "VisitorPointer839" ("VisitorID")
> SELECT "VisitorID" FROM (
> SELECT DISTINCT ON ("VisitorID") "VisitorID","Type"
> FROM "ProgramEvent" WHERE "ProgramID" =3D 10
> ORDER BY "VisitorID","Created" DESC ) v_first
> WHERE v_first."Type" =3D 0;

This gives him a list of all Visitor IDs where the most recent instance of=
=20
that VisitorID is of Type =3D 0;  It's the equivalent of, in SQL-92:

 INSERT INTO "VisitorPointer839" ("VisitorID")
SELECT "VisitorID" FROM=20
	"ProgramEvent" pe1 WHERE EXISTS(
SELECT "VisitorID", MAX("Created")
FROM "ProgramEvent" pe2 WHERE "ProgramID" =3D 10
GROUP BY "VisitorID"=20
HAVING pe2."VisitorID" =3D  pe1."VisitorID"
	AND MAX(pe2."Created") =3D pe1."Created") v_first
	AND "Type" =3D 0;

... which is what I would use, as I dislike database-specific extensions of=
=20
ANSI SQL.

> And Just writing it out straight.
>=20
> insert into VisitorPointer839 ("VisitorID")
> select VisitorID
> from ProgramEvent
> Where ProgramID =3D 10
> and Type =3D 0
> group by VisitorID

This just gives him a list of all VisitorIDs with a Type =3D 0, most recent=
 or=20
not.

> Thanks
> Chad "I wanna be Josh when i grow up" Thompson

<grin> Thanks.   But heck, I'm only 32!  I'm not grown up yet!

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco