From: Raj Mathur <raju@linux-delhi.org>
To: pgsql-sql@postgresql.org
Subject: Re: simple (?) join
Date: Fri, 25 Sep 2009 08:36:48 +0530
Message-ID: <200909250836.48639.raju@linux-delhi.org> (raw)
In-Reply-To: <200909241616.36933.gary.stainburn@ringways.co.uk>
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
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@kandalaya.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: raju@linux-delhi.org
Subject: Re: simple (?) join
In-Reply-To: <200909250836.48639.raju@linux-delhi.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox