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 [email protected].
For more options, visit https://groups.google.com/d/optout.
