[web2py] Re: orderby with groupby in select

m1 = db.messages m2 = db.messages.with_alias('m2') messages = db(m2.timesent==None).select(m1.ALL, left=m2.on((m1.fromid==m2. fromid)&(m1.toid==m1.toid)&(m1.timesent

[web2py] Re: orderby with groupby in select

you can't really group by something and then select all the fields. That's not how grouping works. When you group by something, you MUST also aggregate something else. If you're looking to return the oldest message, what you'd need to ask the database is the minimum date in a group. If you're

[web2py] Re: orderby with groupby in select

uhm... "newest message for each user" was kinda clear ... no ? On Monday, October 5, 2015 at 9:32:29 PM UTC+2, DenesL wrote: > > You probably want the sorting and no grouping: > > messages = db(db.messages.toid == auth.user.id).select(db.messages.ALL, >

[web2py] Re: orderby with groupby in select

You probably want the sorting and no grouping: messages = db(db.messages.toid == auth.user.id).select(db.messages.ALL, orderby=db.messages.fromid|~db.messages.timesent) Denes On Monday, October 5, 2015 at 2:35:16 PM UTC-4, Daniel wrote: > > Hi all, > > I am trying to order messages so that the

[web2py] Re: orderby with groupby in select

I don't know how it would be implemented with DAL syntax, but you are talking about analytical window functions, which are supported by Oracle and Postgres, as far as I know--possibly others. The SQL statement for this in Oracle would look like this, assuming your messages table only has