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.

Reply via email to