<br /><br /> David W Noon wrote: <blockquote cite="mid:20090924194629.44bc236f@dwnoon.ntlworld.com" type="cite"><pre
wrap="">OnThu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL]
simple (?) join:
</pre><blockquote type="cite"><pre wrap="">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? </pre></blockquote><pre wrap="">
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. </pre></blockquote><br /> I don't think that is what he is requesting. I read it he also wants the
timestampincluded in the result set<br /><br /> A nested query <br /><br /> Select <br /> orders.*, <br />
(SELECTMAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid) <br /> From orders<br /><br /> Still
anotheroption is using a join <br /><br /> Select <br /> orders.*, ol_timestamp<br /> From orders <br />
leftjoin (SELECT MAX(ol_timestamp), o_id FROM orders_log group by o_id) as JoinQuery on JoinQuery.o_id = orders.o_id
<br/><br /> The second one should be faster<br /><br /><br /><br />