From: | Raj Mathur <raju(at)linux-delhi(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: simple (?) join |
Date: | 2009-09-25 03:06:48 |
Message-ID: | 200909250836.48639.raju@linux-delhi.org |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thursday 24 Sep 2009, Gary Stainburn wrote:
> 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?
Does this do what you want? You would have to do some tricks if you
also want orders that don't have any entry in the orders_log table to be
displayed.
select ol.ol_id,ol.o_id,ol.ol_timestamp
from orders o natural join orders_log ol
where (ol.o_id,ol.ol_timestamp)
in (select o_id,max(ol_timestamp) from orders_log group by o_id);
Regards,
-- Raju
--
Raj Mathur raju(at)kandalaya(dot)org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
From | Date | Subject | |
---|---|---|---|
Next Message | Oliveiros C, | 2009-09-25 11:00:17 | Re: simple (?) join |
Previous Message | David W Noon | 2009-09-24 21:21:06 | Re: simple (?) join |