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 subject, body, fromid, and timesent:
SELECT MAX(fromid) KEEP (DENSE_RANK LAST ORDER BY timesent) AS fromid
,MAX(subject) KEEP (DENSE_RANK LAST ORDER BY timesent) AS subject
,MAX(body) KEEP (DENSE_RANK LAST ORDER BY timesent) AS body
,MAX(timesent) KEEP (DENSE_RANK LAST ORDER BY timesent) AS timesent
FROM messages
GROUP BY fromid;
For Postgres, I believe it would be something like:
SELECT fromid
,LAST_VALUE(subject) OVER (ORDER BY timesent) AS subject
,LAST_VALUE(body) OVER (ORDER BY timesent) AS body
,LAST_VALUE(timesent) OVER (ORDER BY timesent) AS timesent
FROM messages
GROUP BY fromid;
I made a small test case for the Oracle statement, and confirmed that it
works. I don't have easy access to a Postgres instance, so I'm a bit less
sure of it.
Given the odd/extended syntax of these SQL statements, I don't know whether
it would be possible to use them with the DAL directly. I'd be interested
in knowing if there were.
-d
On Monday, October 5, 2015 at 1:35:16 PM UTC-5, Daniel wrote:
>
> Hi all,
>
> I am trying to order messages so that the newest message from each user is
> displayed. However when grouped by user, the oldest message is displayed
> despite being ordered by timesent. Its like the groupby argument overrides
> the orderby.
>
> messages = db(db.messages.toid == auth.user.id).select(db.messages.ALL,
> orderby=~db.messages.timesent, groupby=db.messages.fromid)
>
>
> Thanks for your help
>
--
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.
