I'm trying to figure out a way to simplify a series of SQL statements. I have the following 2 tables
db.define_table('event_users', Field('event', 'reference events'), Field('user_name', 'reference auth_user'), Field('goal', 'double'), Field('last_entry', 'date'), format='%(user_name)s') db.define_table('entries', Field('user', 'reference event_users'), Field('date_entered', 'date'), Field('value', 'double'), format='%(user)s %(date_entered)s') A user is able make multiple entries per event and I'm trying come up with select statement that gives me the last entry for each user . I also need to maintain a history of entries to display on a different page. So if a user makes the following entries 11/1/2011 100 11/2/2011 200 11/3/2011 300 I have a summary page that will give me the latest entry (in this case 300) and then a detail page that will show all 3 entries. The following works for the most part but its long and it returns a dict inside a row inside a list and is very difficult to display the individual fields. # get users registered for the event user_query = db.event_users.event == event_id users = db(user_query).select() # get the last entry for each user entries = [] for user in users: entry_query = db.entries.user == user entry = db(entry_query).select(orderby=db.entries.date_entered).last() entries.append(entry) Any suggestions? Thanks. Adrian