Try this, has one nested query
sub = session.query(C.id.label('c_id'),
C.d_id.label('d_id'),
func.max(C.value).label('c_maxvalue')
).group_by(C.d_id).subquery()
q = session.query(D.id,sub.c.c_id,sub.c.c_maxvalue).outerjoin(sub)
print q
for row in q:
print 'D-id:%s C-id:%s C-max:%s'%(row.id, row.c_id, row.c_maxvalue)
--
Mike Conley
On Fri, Apr 10, 2009 at 8:55 AM, Joril <jor...@gmail.com> wrote:
>
> On 10 Apr, 12:52, a...@svilendobrev.com wrote:
> > that's what i have in bitemporal queries.
> > u need a groupby and subquery/ies.
>
> I see, thanks for your hint!
> I tried to do it with bare SQL via pgadmin, and I ended up with
>
> select d.id, c.id from d left outer join (
> select c.* from c join (
> select d_id, max(value) as v from c group by d_id) as sub
> on c.d_id = sub.d_id and c.value = sub.v) as c
> on c.d_id = d.id
>
> Now I'll try with the Query API :) But I was wondering, maybe there's
> a simpler way than 3 nested queries...?
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
