you're thinking with a human mind instead of thinking on how a database works... with sets of data.
How can you ask a database to return a single set grouped by something and at the same time as for granular records??? When you use groupby, you can just ask for granular records of the columns you grouped for AND aggregates on every other (min, max, count, sum, avg, count distinct, etc). id--name--variety 1--apple--fruit 2--pear--fruit 3--salad--vegetable 4--spinach--vegetable 5--spinach--vegetable Let's answer "Please, tell me how many varieties I hold"... Two. You can have 2 lines back if you group by variety. No more, no less. "Ok, database, tell me how many of each variety I have" Always two lines, no more, no less. In addition to variety, you ask for the count of each. count--variety 2--fruit 3--vegetable "Ok, database, tell me how many names there are for each variety" Always two lines. In addition, you ask for the count distinct of fruits count distinct--variety 2--fruit 2--vegetable In set theory, THERE'S no way to ask for the id of the original row if you're grouping by something. However, you can nest queries (or use windowing functions, that ATM aren't in pydal) to first ask the database to group, and then select a record that matches the grouped property plus the aggregate you chose, like "the row in the group that has that variety and has the last id (which is max())". the first set would be max(id)--variety 2--fruit 5--vegetable and the second a join to the original, returning 2--pear--fruit 5--spinach--vegetable sooooo.... you can have the latest version_date for each item_id (which correspond to max(version_date) for each group), but you can't ask for anything more which isn't an aggregate, like the item name, in a single shot. At least without resorting to more complicated queries. On Thursday, August 25, 2016 at 7:52:23 AM UTC+2, Encompass solutions wrote: > > I suppose your right, but I was a little thrown, by the: > max(variable_here) > That was mentioned was not the solution at all, I kept looking for ways to > use max as a function. > My issue now, is that the group by doesn't like me getting all the tables > I want in the return. > BR, > Jason Brower > > On Thursday, August 25, 2016 at 8:36:58 AM UTC+3, Dave S wrote: >> >> >> >> On Wednesday, August 24, 2016 at 10:29:09 PM UTC-7, Encompass solutions >> wrote: >>> >>> Does this seem sensible? It seems to work with my initial tests. >>> >>> latest_versions = db( (db.item.id == db.item_version.artifact_id) & >>> (db.item_version.id > 0) >>> >>> ).select(db.item.ALL,db.item_version.version_date.max(), groupby= >>> db.item.id) >>> >>> the .max() feature, at least what I found, was totally undocumented. >>> >> >> Totally? >> You mean, there isn't >> >> <URL: >> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#sum--avg--min--max-and-len >> > >> >> ? >> >> We should have an example database as part of the documentation with a >>> collection of examples around it so we can all relate better. :/ >>> >>> >> Examples in book, using it on severity of logged events. >> >> /dps >> >> >>> 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
