I keep requiring a certain kind of SQL query and I wonder how well web2py supports it. Basically, I need to select the maximum row from each group in a table. I'm sure others have had to do this and I wonder how they've solved the problem through web2py's DAL (i.e., without having to rely on the executesql function).
There is an extremely useful blog post about this (http:// www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/) and I will use their example verbatim: Let’s say I want to select the most recent log entry for each program, or the most recent changes in an audit table, or something of the sort. I’ll re-phrase the question in terms of fruits. I want to select the cheapest fruit from each type. Here’s the desired result: +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 | +--------+----------+-------+ One common solution is a so-called self-join. Step one is to group the fruits by type (apple, cherry etc) and choose the minimum price: select type, min(price) as minprice from fruits group by type; +--------+----------+ | type | minprice | +--------+----------+ | apple | 0.24 | | cherry | 2.55 | | orange | 3.59 | | pear | 2.14 | +--------+----------+ Step two is to select the rest of the row by joining these results back to the same table. Since the first query is grouped, it needs to be put into a subquery so it can be joined against the non-grouped table: select f.type, f.variety, f.price from ( select type, min(price) as minprice from fruits group by type ) as x inner join fruits as f on f.type = x.type and f.price = x.minprice; +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | cherry | bing | 2.55 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 | +--------+----------+-------+ Anyone know how to do this with the DAL in web2py? -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to web...@googlegroups.com. To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
