Re: SQL question re aggregates & joins

On 28 January 2010 21:32, Scott Ribe <scott_ribe(at)killerbytes(dot)com> wrote:

> OK, this does not work:
>
> select max(t1."When"), t1."Pt_Id", t2."DateOfBirth"
> from "PtStaffAccess" t1, "Person" t2
> where t1."Pt_Id" = t2.id
> group by t1."Pt_Id";
>
> But this does:
>
> select max(t1."When"), t1."Pt_Id", min(t2."DateOfBirth")
> from "PtStaffAccess" t1, "Person" t2
> where t1."Pt_Id" = t2.id
> group by t1."Pt_Id";
>
> Now the error message was clear, and I think PG is following the standard
> here. But I have a question just for my own education.
>
> It seems to me, given that "Person".id is declared as the primary key, it
> should possible to deduce that there is no way that the 1st query could
> ever
> have multiple values of "DateOfBirth" to choose from when building a result
> row. Am I missing something? Or am I right, that this is something that SQL
> could do but simply doesn't, for whatever reason, historical, complexity...
>
> In fact, what's even more surprising to me, is that if I change the
> grouping
> to the other side of the join, it still doesn't work:
>
> select max(t1."When"), t2.id, t2."DateOfBirth"
> from "PtStaffAccess" t1, "Person" t2
> where t1."Pt_Id" = t2.id
> group by t2.id;
>
> Come on, I'm grouping on the primary key and it thinks that there might be
> multiple values for the other columns?
>
>
You can't include an aggregate in the select if you don't group by
non-aggregates, so it should be:

select max(t1."When"), t1."Pt_Id", t2."DateOfBirth"
from "PtStaffAccess" t1, "Person" t2
where t1."Pt_Id" = t2.id
group by t1."Pt_Id", t2."DateOfBirth";

and likewise

select max(t1."When"), t2.id, t2."DateOfBirth"
from "PtStaffAccess" t1, "Person" t2
where t1."Pt_Id" = t2.id
group by t2.id, t2."DateOfBirth";

PostgreSQL might already know that there is only 1 date of birth per ID, but
it still doesn't make sense to select them both and group by one of them
when an aggregate is present (even though MySQL will ignore that and just
fill out what it thinks you missed internally)

Regards

Thom