pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: justin <justin@emproshunts.com>
To: David W Noon <dwnoon@ntlworld.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: simple (?) join
Date: Sat, 26 Sep 2009 14:54:24 -0400
Message-ID: <4ABE6360.20205@emproshunts.com> (raw)
In-Reply-To: <20090924222106.23a5c1f3@dwnoon.ntlworld.com>
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
	<20090924194629.44bc236f@dwnoon.ntlworld.com>
	<4ABBD34B.9080600@emproshunts.com>
	<20090924222106.23a5c1f3@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">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
David W Noon wrote:
<blockquote cite="mid:20090924222106.23a5c1f3@dwnoon.ntlworld.com"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">A nested query

Select
   orders.*,
   (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =
orders.oid)
    </pre>
  </blockquote>
  <pre wrap=""><!---->&gt;From orders

That won't give the desired results. I don't think the SQL parser will
even accept it.
  </pre>
</blockquote>
Yes this is a&nbsp; valid SQL statement use similar ones a great deal.&nbsp; The
problem with nested queries is they can only return 1 record per
match.&nbsp; Another problem can be performance it has to run for every
record in order tables and it occurs pre filter conditions <br>
<blockquote cite="mid:20090924222106.23a5c1f3@dwnoon.ntlworld.com"
 type="cite">
  <pre wrap="">
  </pre>
  <blockquote type="cite">
    <pre wrap="">Still another option is using a join

Select
   orders.*, ol_timestamp
   From orders
   left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by
o_id) as JoinQuery on JoinQuery.o_id = orders.o_id
    </pre>
  </blockquote>
  <pre wrap=""><!---->
That won't give the desired results either. If you change "left" to
"inner" you will be closer though.

Both of your queries will retrieve the entire orders table with a
timestamp of some sort from the orders_log table.
  </pre>
</blockquote>
<br>
I read the question as Gary wanted the entire orders table with the
greatest timestamp from the logs table....<br>
<br>
Quoting Gary<br>
<pre wrap="">"How can I select <u><big><big><b>all from orders</b></big></big></u> and the <b><u>last (latest)</u></b> entry from the 
orders_log?"</pre>
<br>
<br>
<br>
<br>
</body>
</html>

view thread (25+ messages)  latest in thread

Message-ID: <4ABE6360.20205@emproshunts.com>
Permalink:  ../4ABE6360.20205@emproshunts.com/
Also on:    postgresql.org/message-id/4ABE6360.20205@emproshunts.com

copy link · copy postgr.es

reply

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
  Subject: Re: simple (?) join
  In-Reply-To: <4ABE6360.20205@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