Re: making queries more effecient

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