Hi villas, thanks for your comment and the links (especially the second one). Since I want to learn if you can show me a better solution in SQL I would highly appreciate it. Normally I prefer do write my SQL "stepwise" so I don't have to read inside out. Here's a small SQLite setup:
-- setup create table prop (id int, name text, price int); insert into prop values (1, 'aaa', 10), (2, 'bbb', 15), (3, 'ccc', 20); create table prop_val (id int, val_date text, amount int); insert into prop_val values (1, '2016-01-01', 12), (1, '2016-01-10', 15), (2, '2016-01-01', 16), (2, '2016-01-10', 17), (3, '2016-01-01', 21), (3, '2016-01-10', 22); -- my query with t1 as ( -- get last valuation for each property (id and date) select id, max(val_date) as last_val_date from prop_val group by id ), t2 as ( -- get last valuation record for each property select pv.* from prop_val pv inner join t1 on t1.id = pv.id and t1.last_val_date = pv.val_date ) select p.name ,p.price ,t2.val_date ,t2.amount from prop p left join t2 on t2.id = p.id -- (expected) result name price val_date amount aaa 10 2016-01-10 15 bbb 15 2016-01-10 17 ccc 20 2016-01-10 22 But still regarding web2py: I've already read the grouping etc. documentation but still find it hard. Often one has to aggregate data and further aggregation is based on that aggregated data (therefore the small example above where one first has to find the max or last date). In SQL that's easy, one just can work with common table expressions; same in R or pandas, where one creates data frames on the fly. But in web2py I cannot "aggregate"/group a table and join the result back to another table, am I wrong? How are web2py professionals are solving that? Iterating through resulting Rows objects and comparing/aggregating them in new data structures? Thanks and best regards, 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.