From: Josh Berkus <josh@agliodbs.com>
To: Chad Thompson <chad@weblinkservices.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: making queries more effecient
Date: Fri, 1 Nov 2002 14:36:32 -0800
Message-ID: <200211011436.32575.josh@agliodbs.com> (raw)
In-Reply-To: <038c01c281f5$fcae7ee0$32021aac@chad>
References: <1036189492.2013.16.camel@localhost.localdomain>
<200211011409.01771.josh@agliodbs.com>
<038c01c281f5$fcae7ee0$32021aac@chad>
Chad,
> Im not sure I can get my head around the difference between doing your
> subselect....
>
> INSERT INTO "VisitorPointer839" ("VisitorID")
> SELECT "VisitorID" FROM (
> SELECT DISTINCT ON ("VisitorID") "VisitorID","Type"
> FROM "ProgramEvent" WHERE "ProgramID" = 10
> ORDER BY "VisitorID","Created" DESC ) v_first
> WHERE v_first."Type" = 0;
This gives him a list of all Visitor IDs where the most recent instance of
that VisitorID is of Type = 0; It's the equivalent of, in SQL-92:
INSERT INTO "VisitorPointer839" ("VisitorID")
SELECT "VisitorID" FROM
"ProgramEvent" pe1 WHERE EXISTS(
SELECT "VisitorID", MAX("Created")
FROM "ProgramEvent" pe2 WHERE "ProgramID" = 10
GROUP BY "VisitorID"
HAVING pe2."VisitorID" = pe1."VisitorID"
AND MAX(pe2."Created") = pe1."Created") v_first
AND "Type" = 0;
... which is what I would use, as I dislike database-specific extensions of
ANSI SQL.
> And Just writing it out straight.
>
> insert into VisitorPointer839 ("VisitorID")
> select VisitorID
> from ProgramEvent
> Where ProgramID = 10
> and Type = 0
> group by VisitorID
This just gives him a list of all VisitorIDs with a Type = 0, most recent or
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!
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, chad@weblinkservices.com
Subject: Re: making queries more effecient
In-Reply-To: <200211011436.32575.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox