Thanks you almost had it,
max = db.track_trace.created_on.max()
having = (db.track_trace.id_status == 11)
rij = db().select(db.track_trace.id_reparaties, db.track_trace.id_status, max,
groupby=~db.track_trace.id_reparaties,having=(db.track_trace.id_status == 11))
The above code is working and gives me the correct amount of rows that have
that status as their newest. However, when I want to do a join in this
query the id_status column shows the first status entered and not the last:
I use this query below, it gives the newest status for that
reparaties_id(with join):
rij = db().select(db.track_trace.id_reparaties, db.track_trace.id_status, db
.track_trace.created_by, max, db.reparaties.created_by, groupby=~db.
track_trace.id_reparaties)
With join:
track_trace.id_reparaties,track_trace.id_status,track_trace.created_by,MAX(track_trace.created_on),reparaties.created_by
12794,1,4,2014-03-13 09:05:33,1
12793,1,5,2014-03-12 14:14:38,1
12792,1,3,2014-03-12 13:20:43,1
12791,1,3,2014-03-12 13:20:34,1
12790,1,5,2014-03-12 15:47:22,1
query without join:
rij = db().select(db.track_trace.id_reparaties, db.track_trace.id_status, db
.track_trace.created_by, max, groupby=~db.track_trace.id_reparaties)
without join:
track_trace.id_reparaties,track_trace.id_status,track_trace.created_by,MAX(track_trace.created_on)
12794,1,4,2014-03-13 09:05:33
12793,1,5,2014-03-12 14:14:38
12792,3,8,2014-03-12 13:20:43
12791,3,8,2014-03-12 13:20:34
12790,13,8,2014-03-12 15:47:22
You can see that in the first example the second column is all 1. In the
second example without the join it gives me the correct status numbers in
the second column. Why does it behave differently with the join or am I
missing something here?
Op donderdag 6 maart 2014 16:10:02 UTC+1 schreef 黄祥:
>
> perhaps something like :
> *not tested*
> max = db.track_trace.created_on.max()
> max_result = db().select(max).first()[max]
>
> query_having = (db.track_trace.id_status == 11)
>
> db().select(db.track_trace.id_reparaties, db.track_trace.id_reparaties,
> max_result,
> groupby = db.track_trace.id_reparaties,
> having = query_having)
>
> best regards,
> stifan
>
--
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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
