From: justin <justin@emproshunts.com>
To: David W Noon <dwnoon@ntlworld.com>
Cc: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: simple (?) join
Date: Thu, 24 Sep 2009 16:15:07 -0400
Message-ID: <4ABBD34B.9080600@emproshunts.com> (raw)
In-Reply-To: <20090924194629.44bc236f@dwnoon.ntlworld.com>
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
<20090924194629.44bc236f@dwnoon.ntlworld.com>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
David W Noon wrote:
<blockquote cite="mid:20090924194629.44bc236f@dwnoon.ntlworld.com"
type="cite">
<pre wrap="">On Thu, 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 timestamp included in the result set<br>
<br>
A nested query <br>
<br>
Select <br>
orders.*, <br>
(SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =
orders.oid) <br>
From orders<br>
<br>
Still another option is using a join <br>
<br>
Select <br>
orders.*, ol_timestamp<br>
From orders <br>
left join (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>
</body>
</html>
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: justin@emproshunts.com, dwnoon@ntlworld.com, gary.stainburn@ringways.co.uk
Subject: Re: simple (?) join
In-Reply-To: <4ABBD34B.9080600@emproshunts.com>
* 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