| From: | justin <justin(at)emproshunts(dot)com> |
|---|---|
| To: | David W Noon <dwnoon(at)ntlworld(dot)com> |
| Cc: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: simple (?) join |
| Date: | 2009-09-26 18:54:24 |
| Message-ID: | 4ABE6360.20205@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">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
David W Noon wrote:
<blockquote cite="mid:20090924222106(dot)23a5c1f3(at)dwnoon(dot)ntlworld(dot)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=""><!---->>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 valid SQL statement use similar ones a great deal. The
problem with nested queries is they can only return 1 record per
match. 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(dot)23a5c1f3(at)dwnoon(dot)ntlworld(dot)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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 1.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David W Noon | 2009-09-26 19:53:49 | Re: simple (?) join |
| Previous Message | justin | 2009-09-26 18:53:24 | Re: simple (?) join |