Re: simple (?) join

----- Original Message -----

From: justin

To: David W Noon

Cc: pgsql-sql@postgresql.org ; gary.stainburn@ringways.co.uk

Sent: Thursday, September 24, 2009 9:15 PM

Subject: Re: [SQL] simple (?) join

David W Noon wrote:

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

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

No joins required.  

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

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

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

The second one should be faster