From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: simple (?) join
Date: Mon, 28 Sep 2009 12:02:20 +0100
User-Agent: KMail/1.9.10
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
	<20090926205349.5fa284f1@dwnoon.ntlworld.com>
	<4ABE7669.50001@emproshunts.com>
In-Reply-To: <4ABE7669.50001@emproshunts.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200909281202.20768.gary.stainburn@ringways.co.uk>

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
>
>  =A0Answer to return the last time someone either viewed or edited the or=
der.
>
>  This is a very common audit requirement to track who what, when and why
> something happened.=A0

=46or 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=20
returning as a join. Specifically I want for each order the most recent log=
=20
entry timestamp and it's associated user - i.e. who made the the last log=20
entry and when.

I suppose I'm asking how I would do the sub-query to pull the most recent l=
og=20
entry per order.


=2D-=20
Gary Stainburn

Gary's Haircut 700
Please visit http://www.justgiving.com/Gary-Stainburn/ to help me=20
raise money for Cancer Research - in return I'll have my head shaved
=20