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," <oliveiros.cristina@marktest.pt>
To: "Gary Stainburn" <gary.stainburn@ringways.co.uk>;
<pgsql-sql@postgresql.org>
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" <gary.stainburn@ringways.co.uk>
> To: <pgsql-sql@postgresql.org>
> 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