Sorry, the previous query was wrong, the "GROUP BY device" was left from a previous more complex example. So, I want to get an equivalent to:
> SELECT p.param, p.date, p.value FROM param_values p INNER JOIN > (SELECT param, max(date) maxdate from param_values GROUP BY > param) m > ON m.param = p.param AND m.maxdate = p.date It looks like this query is also equivalent: SELECT p.param, p.date, p.value FROM param_values p, (SELECT param, max(date) maxdate from param_values GROUP BY param) m WHERE p.param=m.param and p.date = m.maxdate but I still can't find how to do this using the DAL. Greets. On 17 jul, 22:45, Álvaro J. Iradier <[email protected]> wrote: > 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!
