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