You cannot do this with DAL in one query but there may be other ways. What is you model? Do this table have an id field?
If I understand in english: - you have a table called value with fields "indicator", "client_date", "value", "checked" - you first select all distinct values of indicator and for each of them you find the one with the min(client_date) - then if the value of this record is "checked==T" you select the record else you discard it - for each resulting record you select all fields If the table has a unique record "id" this can more easily be done with db.value.client_date.belongs(subselect). Queries this complex are often a sign of design problems. It would be easier to have a column "first" which you set to true for the first entry of each "indicator". Than you select would be very simple and much faster. I personally do not want to support queries like this in web2py. It would be easy to add logic in DAL to support but in my opinion this should be discouraged. On Monday, 29 July 2013 05:00:20 UTC-5, Denis Rykov wrote: > > Please help me to write the following SQL query using DAL: > > SELECT indicator.*, v.date, v.value FROM indicator > LEFT JOIN ( > SELECT value.value, _.indicator, date > FROM (SELECT indicator, min(client_date) AS "date" FROM value > WHERE created_by = 1 GROUP BY indicator) _ > LEFT JOIN value ON _.indicator = value.indicator and _.date = > value.client_date > ) v > ON indicator.id = v.indicator > WHERE indicator.checked = 'T' > -- --- 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/groups/opt_out.
