python - Select first object of group by in postgresql/sqlalchemy -

i have request in sqlachemy sqlite return object of group , value (avg) :

result = session.query(         obj, func.avg(obj.value).label("value_avg")     ).group_by(         func.strftime('%s', obj.date)     ).all() 

but need use postgresql more restrictive (strict sql) , need same thing need replace query(obj) in group func.avg() or else. know if exist func can able return first obj of each group. if not possible maybe can implement comparator obj , instance call func.min(obj) :

result = session.query(         func.min(obj), func.avg(obj.value).label("value_avg")     ).group_by(         func.date_part('second', obj.date)     ).all() 

and maybe implement cmp , eq in obj model ? (what best practice)

edit :

i got workaround i'm not sure it's practice. first group , next join :

sq = session.query(         func.min(obj.date).label("date"), func.avg(obj.value).label("value_avg")     ).group_by(         func.cast(func.extract('second', obj.date) / 600, integer)     ).order_by(obj.date).subquery() result = session.query(obj, sq.c.value_avg).join(sq,sq.c.date == obj.date).all() 

what want first obj of each group , value_avg of group

you need list columns want select , put them in group_by. can select aggregated columns not part of group by.

result = session.query(     obj.column1,     obj.column2,     obj.column3,     func.strftime('%s', obj.date),     func.avg(obj.value).label("value_avg") ).group_by(     obj.column1,     obj.column2,     obj.column3,     obj.date ).all()