Re: Aggregate function with Join stop working under certain condition

From: "Naoko Reeves" <naoko(at)lawlogix(dot)com>
To: "Sam Mason" <sam(at)samason(dot)me(dot)uk>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Aggregate function with Join stop working under certain condition
Date: 2009-08-26 19:57:52
Message-ID: 076DC33A3D38CE4BBC64D35DDD9DE70C08F4DE5A@mse4be2.mse4.exchange.ms
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The solution provide by Sam is unbelievably fast and works 100%
accurately. Thank you very much.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sam Mason
Sent: Wednesday, August 26, 2009 10:40 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Aggregate function with Join stop working under
certain condition

On Wed, Aug 26, 2009 at 11:17:10AM -0400, Naoko Reeves wrote:
> I am joining 4 tables (doc, bpt, con, emp) - emp and doc relationship
is
> one to many. I want to only one doc record per emp as condition shown
> below:

[...]

> However, I wan to add one more doc column but as soon as I add one, it
> try to return all unique doc records. Could you tell me what am I
doing
> wrong here please?

Descriptions of the problem are normally easier to understand than code;
but I *guess* what you want to do is to get the subject of the last
document created by each person and when it was created. If that's the
case then DISTINCT ON is normally the easiest way. Maybe something
like:

SELECT b.bpt_key, e.emp_full_name, c.con_full_name,
d.doc_date_created, d.doc_subject
FROM bpt b, emp e
LEFT JOIN con c ON e.emp_con_key = c.con_key
LEFT JOIN (
SELECT DISTINCT ON (doc_emp_key) doc_emp_key,
doc_date_created, doc_subject
FROM doc
ORDER BY doc_emp_key, doc_date_created DESC) d
ON e.emp_key = d.doc_emp_key
WHERE b.bpt_emp_key = e.emp_key
AND b.bpt_com_key = 22
AND b.bpt_status <> -1;

--
Sam http://samason.me.uk/

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bill Bartlett 2009-08-26 20:09:32 Re: Import data from XML file
Previous Message Martin Gainty 2009-08-26 19:48:04 Re: Import data from XML file