Selecting the one min/max/least/etc row from each group

[jonfroehlich]

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?

[mdipierro]

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 <jonfroehl...@gmail.com> 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...)