Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5B576633D95 for ; Sat, 26 Sep 2009 15:53: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 87073-03 for ; Sat, 26 Sep 2009 18:53:26 +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 4359C633BA2 for ; Sat, 26 Sep 2009 15:53:26 -0300 (ADT) Received: from [192.168.1.45] by mail.emproshunts.com (GMS 15.02.3689/KX5895.00.ca608199) with ESMTP id ixhslaaa for pgsql-sql@postgresql.org; Sat, 26 Sep 2009 14:54:38 -0400 Message-ID: <4ABE6324.3060601@emproshunts.com> Date: Sat, 26 Sep 2009 14:53:24 -0400 From: justin User-Agent: Thunderbird 2.0.0.23 (Windows/20090812) MIME-Version: 1.0 To: "Oliveiros C," 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> <4ABBD34B.9080600@emproshunts.com> <1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt> In-Reply-To: <1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt> 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/125 X-Sequence-Number: 33190

Oliveiros C, wrote:

Hello, Justin, Gary.

Justin, your (the second one) query is not much different from mine.

No its not,

You previewed the possibility of having orders without any matching entry on orders_log with your left join, something that I haven't. Gary, will you have

records on your orders table that don't reference any record on your orders_log table? If so, Justin's query is the right one you should use.

You return the full record from orders and an additional column from orders_log, the ol_timestamp column. I understood that Gary wanted the full record from orders_log, not

just the timestamp column. That part is done by my subquery .

I think Gary could clarify what he wants exactly. Gary? :)

Yes gary please do if these do not answer your questions???

Also, Justin, your query design seems right to me, but maybe you should add this (the part in comment) to your subquery

SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM orders_log group by o_id

because the MAX(ol_timestamp) will receive the name max, not ol_timestamp, and probably

the parser will complain that column ol_timestamp does not exist.

Ain't I right?

Yes you are correct i forgot to add the AS