I've got a table (story_id, votes) with data [(1,3), (2,4)]
when I try to do a query... session.query(table.c.story_id, func.max(table.c.votes)).first() i'll get: (1,4)
the result I expect is: (2,4)
where is the misunderstanding?
A:
You are not grouping by anything so the database can simply return any row with a query like this. If you would add a group_by=table.c.story_id than this would return the proper result.
Most databases would, because of this, block these queries by default since the returned result would be arbitrary. In PostgreSQL for example you would get an error that story_id is not part of an aggregate query and not in the group by clause so it wouldn't know what row to return.
Either way, try this: session.query(table.c.story_id, func.max(table.c.votes)).group_by(table.c.story_id).first()
WoLpH
2010-03-10 18:10:09
thanks for knowladge about block quaries, but recommended query by you gave me this result(1,1) o.O which is still awkward for me :)but nvm, i'll get max using Python .o.for curious cats...the real case is this (which i initially simplified beacuse i thought they're the same, but maybe i'm wrong)table:tsv columns: id, story_id, user_idsub = db.query(tsv.c.story_id, tsv.c.user_id.label('votes')).group_by(tsv.c.story_id).subquery()t = db.query(sub.c.story_id, func.max(sub.c.votes))thanks again :)
xliiv
2010-03-11 12:20:41