Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A91FD634560 for ; Thu, 24 Sep 2009 14:26:46 -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 24454-03 for ; Thu, 24 Sep 2009 17:26:38 +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 8621963425F for ; Thu, 24 Sep 2009 14:26:38 -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 TPHW5MC1; Thu, 24 Sep 2009 18:26:36 +0100 Message-ID: <142D1FF8CDA64A6DAB32AF515A29ED24@marktestcr.marktest.pt> From: "Oliveiros C," To: "Oliveiros C," , "Gary Stainburn" , References: <200909241616.36933.gary.stainburn@ringways.co.uk> <2E407078A6154CF6AFFC6FEFBCF7BFF0@marktestcr.marktest.pt> Subject: Re: simple (?) join Date: Thu, 24 Sep 2009 18:26:35 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit 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.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 200909/118 X-Sequence-Number: 33183 Hmm...no, it seems, it is not allowable to use orders.* on a GROUP BY clause. Unless you've defined for the table something called an ordering operator. If you didn't, you'll have to include all the fields from the orders table in the GROUP BY clause HTH Best, Oliveiros ----- Original Message ----- From: "Oliveiros C," To: "Gary Stainburn" ; Sent: Thursday, September 24, 2009 6:17 PM Subject: Re: [SQL] simple (?) join > You mean > to list the complete orders table and for each of its records, the > corresponding record on the orders_log with the latest ol_timestamp? > > > SELECT * > FROM orders_log main > JOIN > ( > SELECT orders.*, MAX(orders_log.ol_timestamp) as latest > FROM orders > NATURAL JOIN orders_log > GROUP BY orders.* > ) subquery > ON main.ol_timestamp = subquery.latest > AND main.o_id = subquery.o_id > > This query is untested, but could you give it a try? > > Then tell me the results. > > NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it > isnt please kindly substitute by orders.o_id, orders.next_field, etc... > > Best, > Oliveiros > > > ----- Original Message ----- > From: "Gary Stainburn" > To: > Sent: Thursday, September 24, 2009 4:16 PM > Subject: [SQL] simple (?) join > > >> Hi folks. >> >> I have two tables >> >> 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? >> >> Cheers >> -- >> Gary Stainburn >> >> Gary's Haircut 700 >> Please visit http://www.justgiving.com/Gary-Stainburn/ to help me >> raise money for Cancer Research - in return I'll have my head shaved >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql