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
Re: [SQL] simple (?) join
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" To: 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
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.n...@ntlworld.com (David W Noon) === -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
