Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 30482632A48 for ; Thu, 19 Aug 2010 10:25:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 88869-03 for ; Thu, 19 Aug 2010 13:24:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 9B5D96329F3 for ; Thu, 19 Aug 2010 10:24:52 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id QL049SYT; Thu, 19 Aug 2010 14:24:48 +0100 Message-ID: <07AF2300D72042C4BF9FC9D897CEFC15@marktestcr.marktest.pt> From: "Oliveiros d'Azevedo Cristina" To: "Gordon McVey" , References: Subject: Re: Extract created and last modified data Date: Thu, 19 Aug 2010 14:24:48 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002C_01CB3FAA.4759D190" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.791 tagged_above=-5 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201008/73 X-Sequence-Number: 34513 This is a multi-part message in MIME format. ------=_NextPart_000_002C_01CB3FAA.4759D190 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Howdy, Gordon. This query is an attempt to replicate your items table with data just = from changelog table. Could you please try it out? Best, Oliveiros SELECT itm_id,usr_id_create, itm_date_create, usr_id_modify, = itm_date_modify FROM ( SELECT a.itm_id, a.usr_id as usr_id_create, create as itm_date_create FROM changelog a JOIN (SELECT MIN(log_timestamp) as create, itm_id FROM changelog GROUP BY = itm_id) b ON (a.itm_id =3D b.itm_id AND log_timestamp =3D create) ) subquery1 NATURAL JOIN ( SELECT c.itm_id, usr_id as usr_id_modify, last_modified as = itm_date_modify FROM changelog c JOIN (SELECT MAX(log_timestamp) as last_modified, itm_id FROM changelog = GROUP BY itm_id) d ON (d.itm_id =3D c.itm_id AND c.log_timestamp =3D last_modified) ) subquery2 I have a CMS with a table of items, and another table serving as a log = of operations performed on the items. The revelent table structure is = as follows:=20 items=20 itm_id | usr_id_create | itm_date_create | usr_id_modify |=20 itm_date_modify | .....=20 itm_id is a serial primary key.=20 usr_id_* are the keys of the users who created and last modified the=20 item.=20 item_date_* are timestamps for creation and last modification times.=20 changelog=20 itm_id | usr_id | log_timestamp | log_op=20 itm_id and usr_id are foreign keys into the respective item and user=20 tables. log_timestamp is when the last change was made and log_op was = the operation performed.=20 .=20 Can anybody help with writing the query to get a view that replicates=20 the items table except with the create and last modified user IDs and=20 timestamps loaded from the changelog table? If there is anybody who=20 has actually implemented something similar, what kind of performance=20 impact would it have?=20 ------=_NextPart_000_002C_01CB3FAA.4759D190 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Howdy, Gordon.

This query is an attempt to replicate = your items=20 table with data just from changelog table.

Could you please try it = out?

Best,

Oliveiros

SELECT itm_id,usr_id_create, = itm_date_create,=20 usr_id_modify, itm_date_modify

FROM

(

SELECT a.itm_id, a.usr_id as = usr_id_create, create=20 as itm_date_create

FROM =20 changelog a

JOIN

(SELECT MIN(log_timestamp) as create, = itm_id FROM=20 changelog GROUP BY itm_id) b

ON (a.itm_id =3D b.itm_id

AND log_timestamp =3D=20 create)

) subquery1

NATURAL JOIN

(

SELECT c.itm_id, usr_id as = usr_id_modify,=20 last_modified as itm_date_modify

FROM changelog c

JOIN (SELECT MAX(log_timestamp)=20 as last_modified, itm_id FROM changelog GROUP BY itm_id)=20 d

ON (d.itm_id =3D c.itm_id

AND c.log_timestamp =3D = last_modified)

) subquery2

I have a CMS with a table of = items, and=20 another table serving as a log
of operations performed on the = items.=20 The revelent table structure is
as follows:

items
itm_id | usr_id_create | itm_date_create | usr_id_modify = |=20
itm_date_modify | .....

itm_id is a serial primary key.

usr_id_* are the keys of the users who created and last modified = the=20
item.

item_date_* are timestamps for creation and last modification = times.=20

changelog
itm_id | usr_id | log_timestamp | log_op

itm_id and usr_id are foreign keys into the respective item and = user=20
tables. log_timestamp is when the last change was made and = log_op=20 was
the operation performed.

.

Can anybody help with writing the query to get a view that = replicates=20
the items table except with the create and last modified user IDs = and=20
timestamps loaded from the changelog table? If there is = anybody who=20
has actually implemented something similar, what kind of = performance=20
impact would it have?

------=_NextPart_000_002C_01CB3FAA.4759D190--