Re: simple (?) join
От | Oliveiros C, |
---|---|
Тема | Re: simple (?) join |
Дата | |
Msg-id | 9964F6F697654AF4ADAE7DC75B496D05@marktestcr.marktest.pt обсуждение исходный текст |
Ответ на | simple (?) join (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: