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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
