Обсуждение: simple (?) join
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? Cheers -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved
You mean to list the complete orders table and for each of its records, the corresponding record on the orders_log with the latest ol_timestamp? SELECT * FROM orders_log main JOIN ( SELECT orders.*, MAX(orders_log.ol_timestamp) as latest FROM orders NATURAL JOIN orders_log GROUP BY orders.* ) subquery ON main.ol_timestamp = subquery.latest AND main.o_id = subquery.o_id This query is untested, but could you give it a try? Then tell me the results. NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it isnt please kindly substitute by orders.o_id, orders.next_field, etc... Best, Oliveiros ----- Original Message ----- From: "Gary Stainburn" <gary.stainburn@ringways.co.uk> To: <pgsql-sql@postgresql.org> Sent: Thursday, September 24, 2009 4:16 PM Subject: [SQL] simple (?) join > 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? > > Cheers > -- > Gary Stainburn > > Gary's Haircut 700 > Please visit http://www.justgiving.com/Gary-Stainburn/ to help me > raise money for Cancer Research - in return I'll have my head shaved > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Hmm...no, it seems, it is not allowable to use orders.* on a GROUP BY clause. Unless you've defined for the table something called an ordering operator. If you didn't, you'll have to include all the fields from the orders table in the GROUP BY clause HTH Best, Oliveiros ----- Original Message ----- From: "Oliveiros C," <oliveiros.cristina@marktest.pt> To: "Gary Stainburn" <gary.stainburn@ringways.co.uk>; <pgsql-sql@postgresql.org> Sent: Thursday, September 24, 2009 6:17 PM Subject: Re: [SQL] simple (?) join > You mean > to list the complete orders table and for each of its records, the > corresponding record on the orders_log with the latest ol_timestamp? > > > SELECT * > FROM orders_log main > JOIN > ( > SELECT orders.*, MAX(orders_log.ol_timestamp) as latest > FROM orders > NATURAL JOIN orders_log > GROUP BY orders.* > ) subquery > ON main.ol_timestamp = subquery.latest > AND main.o_id = subquery.o_id > > This query is untested, but could you give it a try? > > Then tell me the results. > > NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it > isnt please kindly substitute by orders.o_id, orders.next_field, etc... > > Best, > Oliveiros > > > ----- Original Message ----- > From: "Gary Stainburn" <gary.stainburn@ringways.co.uk> > To: <pgsql-sql@postgresql.org> > Sent: Thursday, September 24, 2009 4:16 PM > Subject: [SQL] simple (?) join > > >> 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? >> >> Cheers >> -- >> Gary Stainburn >> >> Gary's Haircut 700 >> Please visit http://www.justgiving.com/Gary-Stainburn/ to help me >> raise money for Cancer Research - in return I'll have my head shaved >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: >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? 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. -- Regards, Dave [RLU #314465] ======================================================================= david.w.noon@ntlworld.com (David W Noon) =======================================================================
<br /><br /> David W Noon wrote: <blockquote cite="mid:20090924194629.44bc236f@dwnoon.ntlworld.com" type="cite"><pre wrap="">OnThu, 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 timestampincluded in the result set<br /><br /> A nested query <br /><br /> Select <br /> orders.*, <br /> (SELECTMAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid) <br /> From orders<br /><br /> Still anotheroption is using a join <br /><br /> Select <br /> orders.*, ol_timestamp<br /> From orders <br /> leftjoin (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 />
On Thu, 24 Sep 2009 16:15:07 -0400, justin wrote about Re: [SQL] simple (?) join: > > >David W Noon wrote:On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn >wrote about [SQL] simple (?) join: > > 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? > >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. > >I don't think that is what he is requesting. I read it he also wants >the timestamp included in the result set That shouldn't be too much of a problem. >A nested query > >Select > orders.*, > (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = > orders.oid) >From orders That won't give the desired results. I don't think the SQL parser will even accept it. >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 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. >The second one should be faster Try this for an elegant approach: WITH maxi AS (SELECT MAX(ol_timestamp) AS ts FROM orders_log) SELECT o.*, maxi.ts FROM orders AS o WHERE o.o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = maxi.ts); This caches the latest timestamp in a temporary table so that it can both appear in the result set and be used to constrain the selection from the orders_log table. -- Regards, Dave [RLU #314465] ======================================================================= david.w.noon@ntlworld.com (David W Noon) =======================================================================
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 D0EFCC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
Hello, Justin, Gary.
Justin, your (the second one) query is not much different from mine.
You previewed the possibility of having orders without any matching entry on orders_log with your left join, something that I haven't. Gary, will you have
records on your orders table that don't reference any record on your orders_log table? If so, Justin's query is the right one you should use.
You return the full record from orders and an additional column from orders_log, the ol_timestamp column. I understood that Gary wanted the full record from orders_log, not
just the timestamp column. That part is done by my subquery .
I think Gary could clarify what he wants exactly. Gary? :)
Also, Justin, your query design seems right to me, but maybe you should add this (the part in comment) to your subquery
SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM orders_log group by o_id
because the MAX(ol_timestamp) will receive the name max, not ol_timestamp, and probably
the parser will complain that column ol_timestamp does not exist.
Ain't I right?
Best,
Oliveiros
----- Original Message -----From: justinTo: David W NoonSent: Thursday, September 24, 2009 9:15 PMSubject: Re: [SQL] simple (?) join
David W Noon wrote:On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: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?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.
I don't think that is what he is requesting. I read it he also wants the timestamp included in the result set
A nested query
Select
orders.*,
(SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid)
From orders
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
The second one should be faster
> 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? Maby this will work? Get the unique o_id from the table orders and do a group by in a subselect, and then select the other fields from the same table, ie: select o_od, max(o_field1), max(o_field2) ... from orders where o_id in (select o_id from orders group by o_id) group by o_id order by o_id; This off course assumes that o_id is a serial (and thus that timestamp will always be higher etc.). -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare
<br /><br /> Oliveiros C, wrote: <blockquote cite="mid:1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt" type="cite"><style></style><div><fontface="Arial" size="2">Hello, Justin, Gary.</font></div><div> </div><div><font face="Arial"size="2">Justin, your (the second one) query is not much different from mine.</font></div></blockquote> No itsnot, <br /><blockquote cite="mid:1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt" type="cite"><div><font face="Arial"size="2"> You previewed the possibility of having orders without any matching entry on orders_log with yourleft join, something that I haven't. Gary, will you have</font></div><div><font face="Arial" size="2">records on yourorders table that don't reference any record on your orders_log table? If so, Justin's query is the right one you shoulduse.</font></div><div> </div><div><font face="Arial" size="2">You return the full record from orders and an additionalcolumn from orders_log, the ol_timestamp column. I understood that Gary wanted the full record from orders_log,not</font></div><div><font face="Arial" size="2">just the timestamp column. That part is done by my subquery .</font></div><div> </div><div><fontface="Arial" size="2"> I think Gary could clarify what he wants exactly. Gary? :)</font></div></blockquote>Yes gary please do if these do not answer your questions???<br /><blockquote cite="mid:1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt"type="cite"><div> </div><div><font face="Arial" size="2">Also,Justin, your query design seems right to me, but maybe you should add this (the part in comment) to your subquery</font></div><div>SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM orders_log group by o_id </div><div>becausethe MAX(ol_timestamp) will receive the name max, not ol_timestamp, and probably</div><div>the parser willcomplain that column ol_timestamp does not exist.</div><div>Ain't I right?</div></blockquote><br /> Yes you are correcti forgot to add the AS <br />
<br /><br /> David W Noon wrote: <blockquote cite="mid:20090924222106.23a5c1f3@dwnoon.ntlworld.com" type="cite"><blockquotetype="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 nestedqueries is they can only return 1 record per match. Another problem can be performance it has to run for every recordin 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="">Stillanother 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 entireorders table with the greatest timestamp from the logs table....<br /><br /> Quoting Gary<br /><pre wrap="">"How canI 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 />
On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple (?) join: [snip] >Quoting Gary >"How can I select all from orders and the last (latest) entry from the >orders_log?" In that case, a simple Cartesian product will do: SELECT o.*, maxi.ts FROM orders AS o, (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi; Since the cardinality of the subquery "maxi" is 1, it will give a result set with cardinality of the complete orders table. I don't understand why anybody would want to do that. [De gustibus ... ] -- Regards, Dave [RLU #314465] ======================================================================= david.w.noon@ntlworld.com (David W Noon) =======================================================================
David W Noon wrote: <blockquote cite="mid:20090926205349.5fa284f1@dwnoon.ntlworld.com" type="cite"><pre wrap="">On Sat,26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple (?) join: [snip] </pre><blockquote type="cite"><pre wrap="">Quoting Gary "How can I select all from orders and the last (latest) entry from the orders_log?" </pre></blockquote><pre wrap=""> In that case, a simple Cartesian product will do: SELECT o.*, maxi.ts FROM orders AS o, (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi; Since the cardinality of the subquery "maxi" is 1, it will give a result set with cardinality of the complete orders table. I don't understand why anybody would want to do that. [De gustibus ... ] </pre></blockquote><br /> Guessing here <br /><br/> Answer to return the last time someone either viewed or edited the order.<br /><br /> This is a very common auditrequirement to track who what, when and why something happened. <br />
On Saturday 26 September 2009 21:15:37 justin wrote: > David W Noon wrote: > On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple > (?) join: > > [snip] > > Quoting Gary > "How can I select all from orders and the last (latest) entry from the > orders_log?" > > > In that case, a simple Cartesian product will do: > > SELECT o.*, maxi.ts > FROM orders AS o, > (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi; > > Since the cardinality of the subquery "maxi" is 1, it will give a result > set with cardinality of the complete orders table. > > I don't understand why anybody would want to do that. [De gustibus ... ] > > > Guessing here > > Answer to return the last time someone either viewed or edited the order. > > This is a very common audit requirement to track who what, when and why > something happened. For some reason the reply I sent on Friday didn't get through. What I need is all of the order record and all of the latest log entry returning as a join. Specifically I want for each order the most recent log entry timestamp and it's associated user - i.e. who made the the last log entry and when. I suppose I'm asking how I would do the sub-query to pull the most recent log entry per order. -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved
Hello, Gary. thank you for your e-mail This is a slightly modified version of the query I sent you on first place (have you tried it out?). It will return (I hope :) the full orders record plus the maximum ol_timestamp and respective o_user. HTH Best, Oliveiros SELECT subquery.*, orders_log.ol_user FROM orders_log main JOIN ( SELECT orders.o_id, /* The remaining fields of orders go here */, MAX(orders_log.ol_timestamp) as latest FROM orders NATURAL JOIN orders_log GROUP BY orders.o_id, /* the remaining fields of orders go here */ ) subquery ON main.ol_timestamp = subquery.latest AND main.o_id = subquery.o_id ----- Original Message ----- From: "Gary Stainburn" <gary.stainburn@ringways.co.uk> To: <pgsql-sql@postgresql.org> Sent: Monday, September 28, 2009 12:02 PM Subject: Re: [SQL] simple (?) join On Saturday 26 September 2009 21:15:37 justin wrote: > David W Noon wrote: > On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple > (?) join: > > [snip] > > Quoting Gary > "How can I select all from orders and the last (latest) entry from the > orders_log?" > > > In that case, a simple Cartesian product will do: > > SELECT o.*, maxi.ts > FROM orders AS o, > (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi; > > Since the cardinality of the subquery "maxi" is 1, it will give a result > set with cardinality of the complete orders table. > > I don't understand why anybody would want to do that. [De gustibus ... ] > > > Guessing here > > Answer to return the last time someone either viewed or edited the order. > > This is a very common audit requirement to track who what, when and why > something happened. For some reason the reply I sent on Friday didn't get through. What I need is all of the order record and all of the latest log entry returning as a join. Specifically I want for each order the most recent log entry timestamp and it's associated user - i.e. who made the the last log entry and when. I suppose I'm asking how I would do the sub-query to pull the most recent log entry per order. -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
On Mon, 28 Sep 2009 12:02:20 +0100, Gary Stainburn wrote about Re: [SQL] simple (?) join: [snip] >For some reason the reply I sent on Friday didn't get through. >What I need is all of the order record and all of the latest log entry >returning as a join. Specifically I want for each order the most >recent log entry timestamp and it's associated user - i.e. who made >the the last log entry and when. In that case, try this: SELECT o.*, ol.ol_timestamp, ol.ol_user FROM orders AS o INNER JOIN orders_log AS ol ON ol.o_id = o.o_id WHERE ol.ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log AS ol2 WHERE ol2.o_id = o.o_id); This will omit orders that have never been logged. You have not specified what you want done with them. -- Regards, Dave [RLU #314465] ======================================================================= david.w.noon@ntlworld.com (David W Noon) =======================================================================
On Monday 28 September 2009 13:30:32 you wrote: > SELECT o.*, ol.* > FROM orders o > LEFT JOIN ( > SELECT ol.o_id, MAX(ol_timestamp) AS maxts > FROM orders_log ol > GROUP BY 1 > ) max_olt > ON max_olt.o_id=o.o_id > LEFT JOIN orders_log ol > ON ol.o_id=o.o_id > AND ol.ol_timestamp=max_olt.maxts > ; Cheers Jim, That worked. Gary -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved