Thanks, i'll checkout the lateral joins. :) In the case of the clean API, the sql i produced is SQL-92 compatible, is that clean enough? :) As for iterating over the results in python doesn't seem like an all to valid option with some larger tables. (We use Peoplesoft HR with years of history resulting in tables with millions of rows like this) - Think i will stick to crafted SQL for now!
Remco Op maandag 8 december 2014 19:20:20 UTC+1 schreef Niphlod: > > given that the aliases are not supported in master-sub selects (i.e. > they're coded with the same level of "aliasing" in mind, whereas your > select needs to propagate a carefully crafted alias in some place), the > problem relies on the fact that subselect are only allowed to have multiple > "conditions" to be specified. > Moreover, what you're actually lacking (or, from another perspective, DAL > is lacking) is access to window functions > In this particular case it's not needed, and you could ,e.g., use a Set to > specify a two-column resultset that needs to be matched with another table > to give you the result, we'll be ok.... The first set would be a (emp_id, > max_date) retrieved with a groupby on the emp_id and the second set to > match would be the (id, date) tuple to use to match the name. > > You can do it with two selects and merging sets with python, or.... figure > out a clean API that is supported on most of the backends :-P > > > BTW: check the "LATERAL" joins that are available in postgresql 9.3 ... > will probably blow your head > -- 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.
