Re: EOAndQualifier order
Re: EOAndQualifier order
- Subject: Re: EOAndQualifier order
- From: Q <email@hidden>
- Date: Thu, 12 Jul 2007 23:39:49 +1000
On 12/07/2007, at 9:58 PM, Ken Anderson wrote: Actually, Sybase will accept the query. It allows group by on fields that are not in the select.
Grouping by fields not used in the select clause is not strictly legal SQL either (I don't think), and I can't think of a case you would actually want to do that off hand, but even so the result should be deterministic. The issue I am referring to is attempting to select from fields that aren't in the group by clause. If your database accepts this syntax, it's broken. The reason being that the result is non-determinate because you are asking the database to aggregate a set of possible results into a single value without any selection criteria.
Consider the case where the previously mentioned table had a middleName field. If you were to SELECT * ... GROUP BY departmentId, and there are two Bob Smith's with different middle names in a single department, which middle name should be returned?
On Jul 12, 2007, at 2:55 AM, Q wrote: This query is invalid, your database should reject it.
It should be:
SELECT departmentId FROM foo WHERE lastName='Smith' AND firstName='Bob' GROUP BY departmentId
which is the same as writing:
SELECT DISTINCT departmentId FROM foo WHERE lastName='Smith' AND firstName='Bob'
-- Seeya...Q
Quinton Dolan - email@hidden Gold Coast, QLD, Australia (GMT+10) Ph: +61 419 729 806
|
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden