Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 3A047634560
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 24 Sep 2009 17:27:33 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 53163-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 24 Sep 2009 20:27:18 +0000 (UTC)
X-Greylist: delayed 00:12:11.632214 by SQLgrey-1.7.6
Received: from mail.emproshunts.com (mail.emproshunts.com [66.64.191.19])
	by mail.postgresql.org (Postfix) with ESMTP id 97C00633B84
	for <pgsql-sql@postgresql.org>; Thu, 24 Sep 2009 17:27:21 -0300 (ADT)
Received: from [192.168.1.45] by mail.emproshunts.com (GMS
	15.02.3689/KX5895.00.ca608199) with ESMTP id vvwrlaaa for
	pgsql-sql@postgresql.org; Thu, 24 Sep 2009 16:16:19 -0400
Message-ID: <4ABBD34B.9080600@emproshunts.com>
Date: Thu, 24 Sep 2009 16:15:07 -0400
From: justin <justin@emproshunts.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: David W Noon <dwnoon@ntlworld.com>
CC: pgsql-sql@postgresql.org, gary.stainburn@ringways.co.uk
Subject: Re: simple (?) join
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
	<20090924194629.44bc236f@dwnoon.ntlworld.com>
In-Reply-To: <20090924194629.44bc236f@dwnoon.ntlworld.com>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Originating-IP: [192.168.1.45]
X-DNSBL: 0
X-AntiSpam: Checked for restricted content by Gordano's AntiSpam Software
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.273 tagged_above=-10 required=5
	tests=BAYES_40=-0.185, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457
X-Spam-Level: *
X-Archive-Number: 200909/120
X-Sequence-Number: 33185

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
David W Noon wrote:
<blockquote cite="mid:20090924194629.44bc236f@dwnoon.ntlworld.com"
 type="cite">
  <pre wrap="">On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL]
simple (?) join:

  </pre>
  <blockquote type="cite">
    <pre wrap="">create table orders (
o_id serial primary key
...
);

create table orders_log (
ol_id serial primary key,
o_id int4 not null references orders(o_id),
ol_timestamp timestamp,
ol_user,
);

How can I select all from orders and the last (latest) entry from the 
orders_log?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
SELECT * FROM orders
WHERE o_id IN (SELECT o_id FROM orders_log
   WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log));

No joins required.
  </pre>
</blockquote>
<br>
I don't think that is what he is requesting.&nbsp; I read it he also wants
the timestamp included in the result set<br>
<br>
A nested query <br>
<br>
Select <br>
&nbsp;&nbsp;&nbsp; orders.*, <br>
&nbsp;&nbsp;&nbsp; (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =
orders.oid) <br>
From orders<br>
<br>
Still another option is using a join <br>
<br>
Select <br>
&nbsp;&nbsp;&nbsp; orders.*, ol_timestamp<br>
&nbsp;&nbsp;&nbsp; From orders <br>
&nbsp;&nbsp;&nbsp; left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by
o_id) as JoinQuery on JoinQuery.o_id = orders.o_id <br>
<br>
The second one should be faster<br>
<br>
<br>
<br>
</body>
</html>