From: David W Noon <dwnoon@ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Re: simple (?) join
Date: Mon, 28 Sep 2009 12:27:16 +0100
Message-ID: <20090928122716.61b99cfb@dwnoon.ntlworld.com> (raw)
In-Reply-To: <200909281202.20768.gary.stainburn@ringways.co.uk>
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
<20090926205349.5fa284f1@dwnoon.ntlworld.com>
<4ABE7669.50001@emproshunts.com>
<200909281202.20768.gary.stainburn@ringways.co.uk>
On Mon, 28 Sep 2009 12:02:20 +0100, Gary Stainburn wrote about Re:
[SQL] simple (?) join:
[snip]
>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.
In that case, try this:
SELECT o.*, ol.ol_timestamp, ol.ol_user
FROM orders AS o
INNER JOIN orders_log AS ol
ON ol.o_id = o.o_id
WHERE ol.ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log AS
ol2 WHERE ol2.o_id = o.o_id);
This will omit orders that have never been logged. You have not
specified what you want done with them.
--
Regards,
Dave [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dwnoon@ntlworld.com
Subject: Re: simple (?) join
In-Reply-To: <20090928122716.61b99cfb@dwnoon.ntlworld.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox