Hi, I have a table with a record of values read from a device. Table
has a parameter ID, the record date, and the parameter value:
db.define_table('param_values',
Field('param', db.dev_parameter, required=True, notnull=True),
Field('date', 'datetime', required=True, notnull=True),
Field('value', 'integer', required=True, notnull=True),
)
I'm trying to get the latest recorded value (according to the 'date'
field) for each parameter, in a single query.
In MySQL, I can run the following query:
SELECT p.param, p.date, p.value FROM param_values p INNER JOIN
(SELECT param, max(date) maxdate from param_values GROUP BY device,
param) m
ON m.param = p.param AND m.maxdate = p.date
but I can't find a way to build a similar query in Web2py DAL, as the
inner joins in DAL are really built using a WHERE.
The problem is I want to join with two values in the subquery. I must
use a subquery in order to use the grouping to get the max date for
each different param...
Any idea how could I do something similar using the DAL? Or will I
need to use raw SQL?
Thanks very much!
