hi
object A has 2 columns, x and y. From all the A instances, for each distinct
x, i want to get the instance that has maximum y.
(it's a temporal query, x is obj_id, y is time - yielding the latest version
of all the objects)
so far i invented this sql:
select a.* from
a,
(select x,max(y) as y from a group by x) as r
where a.x==r.x and a.y==r.y;
(it is a bit weak relying on joining on y-value...)
and in SA:
class A: ...
atable = class_mapper(A).local_table
r = select( [ atable.c.x.label( 'mx'), func.max( atable.c.y).label( 'my')] )
.group_by( atable.c.x)
q = session.query(A).filter( (A.x==r.c.mx) & (A.y==r.c.my) )
can it be done neater? e.g. without separate select/atable...
thanks
svil
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
