Found the answer to display the max value record in each group:
db.define_table('test', Field('key'), Field('value'))
max = db.test.with_alias('max')
query = max.value == None
left = max.on((max.key == db.test.key) & (max.value > db.test.value))
grid = SQLFORM.grid(query,left=left)
On Friday, December 7, 2012 9:26:20 AM UTC-5, Mark wrote:
>
> Hello,
>
> I have following records in a table for example:
>
> id, key, value
> 10, AA, 838
> 11, BB, 135
> 12, BB, 147
> 13, AA, 400
> 14, AA, 999
> 15, AA, 625
>
> db.define_table('test', Field('key'), Field('value'))
>
> I want to display the max value in each key group in the grid, such as:
> 12, BB, 147
> 14, AA, 999
>
> query = db.test
> grid = SQLFORM.grid(query, orderby=~db.test.value, groupby=db.test.key)
> grid will display:
> 15, AA, 625
> 12, BB, 147
>
> I think the groupby always return the max(id) record. The groupby depend
> on record id instead of orderby. How can I modify the query or grid to
> display the max value in each group?
>
> Any help will be greatly appreciated.
>
> Mark
>
>
>
--
