Hi villas,
perhaps my "problem" is: if you group/aggregate in SQL, you are allowed
only to put attributes in the select-clause which are either grouped (i.e.
which also appear in the group-clause) or which are aggregated (e.g. with
sum, max, min, avg) at least as far as my knowledge goes. The following is
allowed/valid, because it is grouped by a and b, c and d are aggregated.
SELECT a, b, sum(c), sum(d)
FROM mytable
GROUP BY a, b
The following isn't allowed, because e is not aggregated nor grouped.
SELECT a, b, sum(c), sum(d), e
FROM mytable
GROUP BY a, b
The point is: the second query is e.g. allowed by SQLite - if you do the
same query in e.g. MS SQL Server, you'll get an error message because e has
to be either aggregated with an function or it has to be in the
group-by-clause.
Your web2py-code/-example generates the following SQL statement
SELECT prop.name, MAX(prop_val.val_date), prop_val.amount, prop.price
FROM prop_val, prop
WHERE (prop.id = prop_val.prop_id)
GROUP BY prop.name;
which has the above described "problem"; it will be accepted by SQLite but
e.g. not by SQL Server because prop_val.amount and prop.price aren't in the
group-by-clause nor are they aggregated. Perhaps web2py/SQLite delivers the
right result by random or SQLite has another algorithm in the background
and is more intelligent than MS SQL Server :-), I don't know.
Thanks for your effort and example!
stex
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
