Re: SQL question re aggregates & joins

[Thom Brown <thombrown@xxxxxxxxx>]

On 28 January 2010 21:32, Scott Ribe <scott_ribe@xxxxxxxxxxxxxxx> 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