On 5/16/2014 11:02 AM, Rob Richardson wrote:
It took me a bit of looking, but I think I understand your query. One question remains: why did you use the max() function?
It is, technically, not legal in SQL to use both an aggregate function and a direct field access on the same table. Use of an aggregate function means that an aggregation is performed, that multiple rows of the input table are grouped together in some way to produce one row of the result. If you also access a filed directly, without specifying how it should be aggregated, then the engine wouldn't know which of the many rows in the group the value in the output should be coming from.
Now, in this particular case the issue is moot, for two reasons. First, we are explicitly checking for count(*)=1, thus only dealing with groups that contain exactly one row, and so the ambiguity fails to arise. However, while it's obvious to a human reading the statement, it wouldn't generally be obvious to the SQL engine parsing it - requiring it to understand the semantics of the query to such an extent is really asking too much.
Second, SQLite allows this mixing of aggregate functions and raw fields, as a non-standard extension. When you do that, it just picks the value from an arbitrary row in the group (I'm simplifying a bit here) - which is not a problem in our case, since we know that the group consists on one row anyway.
So with SQLite, the query without max() would work, and produce expected results. With another database engine that enforces SQL rules more strictly, the query without max() would fail with a syntax error. I figured I'd do it by the book and wrap the field into an aggregate function. I could have used max(), or min(), or avg() - it doesn't matter which, they all work the same when there's only one row to aggregate.
-- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
