Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 9CB21633633
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 26 Sep 2009 15:54:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 86993-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 26 Sep 2009 18:54:27 +0000 (UTC)
X-Greylist: from auto-whitelisted 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 CEDCE632AB3
	for <pgsql-sql@postgresql.org>; Sat, 26 Sep 2009 15:54:26 -0300 (ADT)
Received: from [192.168.1.45] by mail.emproshunts.com (GMS
	15.02.3689/KX5895.00.ca608199) with ESMTP id txhslaaa for
	pgsql-sql@postgresql.org; Sat, 26 Sep 2009 14:55:39 -0400
Message-ID: <4ABE6360.20205@emproshunts.com>
Date: Sat, 26 Sep 2009 14:54:24 -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
Subject: Re: simple (?) join
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
	<20090924194629.44bc236f@dwnoon.ntlworld.com>
	<4ABBD34B.9080600@emproshunts.com>
	<20090924222106.23a5c1f3@dwnoon.ntlworld.com>
In-Reply-To: <20090924222106.23a5c1f3@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.141 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457
X-Spam-Level: 
X-Archive-Number: 200909/126
X-Sequence-Number: 33191

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
David W Noon wrote:
<blockquote cite="mid:20090924222106.23a5c1f3@dwnoon.ntlworld.com"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">A nested query

Select
   orders.*,
   (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =
orders.oid)
    </pre>
  </blockquote>
  <pre wrap=""><!---->&gt;From orders

That won't give the desired results. I don't think the SQL parser will
even accept it.
  </pre>
</blockquote>
Yes this is a&nbsp; valid SQL statement use similar ones a great deal.&nbsp; The
problem with nested queries is they can only return 1 record per
match.&nbsp; Another problem can be performance it has to run for every
record in order tables and it occurs pre filter conditions <br>
<blockquote cite="mid:20090924222106.23a5c1f3@dwnoon.ntlworld.com"
 type="cite">
  <pre wrap="">
  </pre>
  <blockquote type="cite">
    <pre wrap="">Still another option is using a join

Select
   orders.*, ol_timestamp
   From orders
   left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by
o_id) as JoinQuery on JoinQuery.o_id = orders.o_id
    </pre>
  </blockquote>
  <pre wrap=""><!---->
That won't give the desired results either. If you change "left" to
"inner" you will be closer though.

Both of your queries will retrieve the entire orders table with a
timestamp of some sort from the orders_log table.
  </pre>
</blockquote>
<br>
I read the question as Gary wanted the entire orders table with the
greatest timestamp from the logs table....<br>
<br>
Quoting Gary<br>
<pre wrap="">"How can I select <u><big><big><b>all from orders</b></big></big></u> and the <b><u>last (latest)</u></b> entry from the 
orders_log?"</pre>
<br>
<br>
<br>
<br>
</body>
</html>