Hello, Gary.
thank you for your e-mail
This is a slightly modified version of the query I sent you on first place
(have you tried it out?).
It will return (I hope :) the full orders record plus the maximum
ol_timestamp and respective o_user.
HTH
Best,
Oliveiros
SELECT subquery.*, orders_log.ol_user
FROM orders_log main
JOIN
(
SELECT orders.o_id, /* The remaining fields of orders go here */,
MAX(orders_log.ol_timestamp) as latest
FROM orders
NATURAL JOIN orders_log
GROUP BY orders.o_id, /* the remaining fields of orders go here */
) subquery
ON main.ol_timestamp = subquery.latest
AND main.o_id = subquery.o_id
----- Original Message -----
From: "Gary Stainburn" <gary.stainb...@ringways.co.uk>
To: <pgsql-sql@postgresql.org>
Sent: Monday, September 28, 2009 12:02 PM
Subject: Re: [SQL] simple (?) join
On Saturday 26 September 2009 21:15:37 justin wrote:
David W Noon wrote:
On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple
(?) join:
[snip]
Quoting Gary
"How can I select all from orders and the last (latest) entry from the
orders_log?"
In that case, a simple Cartesian product will do:
SELECT o.*, maxi.ts
FROM orders AS o,
(SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi;
Since the cardinality of the subquery "maxi" is 1, it will give a result
set with cardinality of the complete orders table.
I don't understand why anybody would want to do that. [De gustibus ... ]
Guessing here
Answer to return the last time someone either viewed or edited the order.
This is a very common audit requirement to track who what, when and why
something happened.
For some reason the reply I sent on Friday didn't get through.
What I need is all of the order record and all of the latest log entry
returning as a join. Specifically I want for each order the most recent log
entry timestamp and it's associated user - i.e. who made the the last log
entry and when.
I suppose I'm asking how I would do the sub-query to pull the most recent
log
entry per order.
--
Gary Stainburn
Gary's Haircut 700
Please visit http://www.justgiving.com/Gary-Stainburn/ to help me
raise money for Cancer Research - in return I'll have my head shaved
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
