First of all I would recommend that you do not call your table "items" but "item" since db.items is a method. Also "value" is a reserver keyword in SQL so call the Field "val".
What you want to do is discussed here: http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results It cannot be done in dal because it requires a nested select. Dal does not support nested selects. Yet nested selects are in-efficient. My recommendation is do something different. Add a column "is_max" boolean and update its value value every time a record is inserted. On Thursday, 17 July 2014 19:19:33 UTC-5, asumal wrote: > > I have the following model: > > db.define_table('items', Field('part_no'), Field('value')) > > I would like to have the records with the highest value for each part_no. > > Here are some sample data: > > > ID Part No Value > 1 a 50 > 2 a 80 > 3 b 60 > 4 b 20 > What I want as a result: > > > ID Part No Value > 2 a 80 > 3 b 60 > > > I would also like to use this in a SQLFORM.grid() data structure, so I > need help coming up with a query to do so. > -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
