I think I understand and you are right, DBs normally require the fielded listed in the GROUP BY and ORDER BY too.
However, the DAL enables you to build such queries, so I'm not sure what the problem is. However, I hope you have found a solution now. Best wishes. On Thursday, 26 May 2016 20:11:27 UTC+1, ste...@gmail.com wrote: > > 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
