To clarify a bit, what I'm trying to do is very similar to this: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ But with two tables that need to be joined, with dates instead of prices, and with a "target date" that serves as a cutoff point for the highest date (no events with dates after the target date should end up in the results). I want one result per member in the resulting rows. And I'd prefer to do it through the DAL, because if I don't, I'll have to pack the results (list of tupples) into a list dictionaries, which is too time consuming for the amount of records that I'm dealing with (any iterative approach in python is out).
Thanks, Joe G. On Jun 20, 11:50 pm, "Joseph S. Greenawalt" <[email protected]> wrote: > Sorry for the long subject line. I've got the following tables > (extraneous fields removed): > > db.define_table('Members',\ > Field("FirstName", "string"),\ > Field("LastName", "string")) > > db.define_table('MemberEvents',\ > Field("MemberID", "integer"),\ > Field("EventType", "string"),\ > Field("EventDate", "string")) > > The MemberEvents table holds multiple events for each member. Some > members may have no events; I want them to show up in the results as > well, but with Null/None values for the MemberEvents fields. What I'm > trying to do is join these two tables in such a way that I end up with > only the event that has the highest date that is less than a "target > date" parameter for each member. I've tried "groupby", but I can't > find a way to control which event is selected when the groups are > "collapsed". I've tried all kinds of things, but with no luck so > far. Here's my latest attempt (doesn't work): > > maxDate = db.MemberEvents.EventDate.max() > results = db((db.MemberEvents.EventDate<=date)).select(\ > db.Members.ALL,\ > db.CaseManagers.ALL,\ > maxDate,\ > left=\ > db.MemberEvents.on(db.Members.id == db.MemberEvents.MemberID),\ > groupby=db.Members.id,\ > having=(db.MemberEvents.EventDate==maxDate)) > > This gives an "Unknown column 'MemberEvents.EventDate' in 'having > clause'" error. Any help would be greatly appreciated. I'd even be > willing to paypal $30 to anyone who can solve this with the DAL (and > provide an explanation of the solution) -- without iterating over the > records in python (slow, I've got 10000+ event records). I need this > to work quickly with lots of records. I've been stuck on this for > days and have a deadline rapidly approaching. > > Thanks in advance, > Joe G.
