Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A05B6633FFE for ; Fri, 25 Sep 2009 08:00: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 96530-04 for ; Fri, 25 Sep 2009 11:00:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 532C6632356 for ; Fri, 25 Sep 2009 08:00:21 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id TPHW53L0; Fri, 25 Sep 2009 12:00:18 +0100 Message-ID: <1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt> From: "Oliveiros C," To: "justin" Cc: , References: <200909241616.36933.gary.stainburn@ringways.co.uk> <20090924194629.44bc236f@dwnoon.ntlworld.com> <4ABBD34B.9080600@emproshunts.com> Subject: Re: simple (?) join Date: Fri, 25 Sep 2009 12:00:17 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0110_01CA3DD7.BFBC6BA0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.091 tagged_above=-10 required=5 tests=AWL=0.093, BAYES_40=-0.185, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200909/123 X-Sequence-Number: 33188 This is a multi-part message in MIME format. ------=_NextPart_000_0110_01CA3DD7.BFBC6BA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, Justin, Gary. Justin, your (the second one) query is not much different from mine. 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? :) Also, Justin, your query design seems right to me, but maybe you should = add this (the part in comment) to your subquery=20 SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM orders_log = group by o_id=20 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? Best, Oliveiros ----- Original Message -----=20 From: justin=20 To: David W Noon=20 Cc: pgsql-sql@postgresql.org ; gary.stainburn@ringways.co.uk=20 Sent: Thursday, September 24, 2009 9:15 PM Subject: Re: [SQL] simple (?) join David W Noon wrote:=20 On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: 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=20 orders_log? =20 SELECT * FROM orders WHERE o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp =3D (SELECT MAX(ol_timestamp) FROM orders_log)); No joins required. =20 I don't think that is what he is requesting. I read it he also wants = the timestamp included in the result set A nested query=20 Select=20 orders.*,=20 (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = =3D orders.oid)=20 From orders Still another option is using a join=20 Select=20 orders.*, ol_timestamp From orders=20 left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by = o_id) as JoinQuery on JoinQuery.o_id =3D orders.o_id=20 The second one should be faster ------=_NextPart_000_0110_01CA3DD7.BFBC6BA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello, Justin, Gary.
 
Justin, your (the second one) = query is not=20 much different from mine.
 You previewed  the = possibility of=20 having orders without any matching entry on orders_log with your = left join,=20 something that I haven't. Gary, will you have
records on your orders table that don't = reference=20 any record on your orders_log table? If so, Justin's query is the right = one you=20 should use.
 
You return the full record from orders = and an=20 additional column from orders_log, the ol_timestamp column. I = understood=20 that Gary wanted the full record from orders_log, not
just the timestamp column. That = part is done=20 by my subquery .
 
 I think Gary could clarify what = he wants=20 exactly. Gary? :)
 
Also, Justin, your query = design seems right to=20 me, but maybe you should add this (the part in comment) to your = subquery=20
SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM = orders_log group=20 by o_id
because the MAX(ol_timestamp) will receive the name max, not = ol_timestamp,=20 and probably
the parser will complain that column ol_timestamp  does = not=20 exist.
Ain't I right?
 
Best,
Oliveiros
 
 
----- Original Message -----
From:=20 justin
Cc: pgsql-sql@postgresql.org = ; gary.stainburn@ringways.co.= uk=20
Sent: Thursday, September 24, = 2009 9:15=20 PM
Subject: Re: [SQL] simple (?) = join



David W Noon = wrote:=20 
On Thu, 24 Sep 2009 16:16:36 +0100, Gary =
Stainburn wrote about [SQL]
simple (?) join:

  
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=20
orders_log?
    

SELECT * FROM orders
WHERE o_id IN (SELECT o_id FROM orders_log
   WHERE ol_timestamp =3D (SELECT MAX(ol_timestamp) FROM orders_log));

No joins required.

I don't think that is what he is = requesting.  I=20 read it he also wants the timestamp included in the result = set

A nested=20 query

Select
    orders.*, =
   =20 (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =3D = orders.oid)=20
From orders

Still another option is using a join =

Select=20
    orders.*, ol_timestamp
    = From=20 orders
    left join (SELECT MAX(ol_timestamp), = o_id FROM=20 orders_log group by o_id) as JoinQuery on JoinQuery.o_id =3D = orders.o_id=20

The second one should be=20 faster



------=_NextPart_000_0110_01CA3DD7.BFBC6BA0--