you can do
rows = db().select(db.fruit.type,db.fruit.price.min
(),groupby=db.fruit.type)
for row in rows: print row.fruit.type, row._extra[db.fruit.price.min
()]
gives you
| apple | 0.24 |
| cherry | 2.55 |
| orange | 3.59 |
| pear | 2.14 |
The second case. I can do it with two queries but I am not sure it
works on every backend (works with sqlite)
tmp = db().select(db.fruit.id,db.fruit.price.min
(),groupby=db.fruit.type)
rows = db(db.fruit.id.belongs([r.id for r in tmp])).select()
for row in rows: print row.type, row.variety, row.price
| apple | fuji | 0.24 |
| cherry | bing | 2.55 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
I cannot think of an easier way. Probably it is easier to user raw SQL
as
rows = db.executesql("...",as_dict=True)
Massimo
On Dec 17, 10:48 pm, jonfroehlich <[email protected]> wrote:
> 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...)
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
