what you want is the latest version for each item_id . That is the row having the greatest version_date if you divide your dataset for each item_id.
that is what groupby item_id does..... and what max(version_date) does too. On Tuesday, August 23, 2016 at 11:05:52 AM UTC+2, Encompass solutions wrote: > > This document doesn't mention your method or using max() > > > http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#sum-avg-min-max-and-len > > Or I don't understand how you would do it. > Could you provide greater detail on how to build that query? > BR, > Jason > > On Monday, August 22, 2016 at 10:52:55 AM UTC+3, Encompass solutions wrote: >> >> Consider the following pseudo model. >> >> item >> ->name = "string" >> >> version >> ->item_id = item.id >> ->version_date = "datetime" >> >> >> While I can easily create a collection of the item with it's versions. >> all_items = db((db.item.id > 0) & (db.version.item_id == db.item.id >> )).select(orderby=db.item.name | db.version.version_date) >> >> How do get just all items with just the latest version of each item >> without having to do this.... >> items = [] >> current_id = all_items.first().item.id >> for thing in all_items: >> if thing.item.id != current_id: >> current_id = thing.item.id >> items.append(thing) >> >> It seems a bit silly and heavy to be doing this especially since my data >> could get quite large. I imaging the database has some way to do this, >> just never learned how. >> >> Ideas on how this could be done? >> >> BR, >> Jason Brower >> >> -- 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.
