Re: simple (?) join

From: justin <justin(at)emproshunts(dot)com>
To: David W Noon <dwnoon(at)ntlworld(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, gary(dot)stainburn(at)ringways(dot)co(dot)uk
Subject: Re: simple (?) join
Date: 2009-09-24 20:15:07
Message-ID: 4ABBD34B.9080600@emproshunts.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

<!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(dot)44bc236f(at)dwnoon(dot)ntlworld(dot)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.&nbsp; I read it he also wants
the timestamp included in the result set<br>
<br>
A nested query <br>
<br>
Select <br>
&nbsp;&nbsp;&nbsp; orders.*, <br>
&nbsp;&nbsp;&nbsp; (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>
&nbsp;&nbsp;&nbsp; orders.*, ol_timestamp<br>
&nbsp;&nbsp;&nbsp; From orders <br>
&nbsp;&nbsp;&nbsp; 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>

Attachment Content-Type Size
unknown_filename text/html 1.6 KB

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David W Noon 2009-09-24 21:21:06 Re: simple (?) join
Previous Message David W Noon 2009-09-24 18:46:29 Re: simple (?) join