If you are okay with only getting one record in the case of ties you can do session.query(Snapshot).order_by(Snapshot.totalqty.desc()).first()
On Fri, Nov 7, 2008 at 12:22 PM, John Hunter <[EMAIL PROTECTED]> wrote: > > I am having trouble writing a sqlalchemy query which selects all rows > where a field equals the max for that field, eg > > > q = > session.query(Snapshot).filter(Snapshot.totalqty==func.max(Snapshot.totalqty)) > > When I try and get the results of the query, I get the error below. > How should I use func.max here? > > __version__ = 0.5.0rc3 > > In [54]: len(q.all()) > ------------------------------------------------------------ > Traceback (most recent call last): > File "<ipython console>", line 1, in ? > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py", > line 994, in all > return list(self) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py", > line 1082, in __iter__ > return self._execute_and_instances(context) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py", > line 1085, in _execute_and_instances > result = self.session.execute(querycontext.statement, > params=self._params, mapper=self._mapper_zero_or_none(), > _state=self._refresh_state) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/session.py", > line 749, in execute > return self.__connection(engine, close_with_result=True).execute( > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py", > line 826, in execute > return Connection.executors[c](self, object, multiparams, params) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py", > line 877, in execute_clauseelement > return self._execute_compiled(elem.compile(dialect=self.dialect, > column_keys=keys, inline=len(params) > 1), distilled_params=params) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py", > line 889, in _execute_compiled > self.__execute_raw(context) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py", > line 898, in __execute_raw > self._cursor_execute(context.cursor, context.statement, > context.parameters[0], context=context) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py", > line 942, in _cursor_execute > self._handle_dbapi_exception(e, statement, parameters, cursor) > File > "/home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py", > line 924, in _handle_dbapi_exception > raise exc.DBAPIError.instance(statement, parameters, e, > connection_invalidated=is_disconnect) > ProgrammingError: (ProgrammingError) (1111, 'Invalid use of group > function') u'SELECT snapshot.symbol AS snapshot_symbol, > snapshot.strategy AS snapshot_strategy, snapshot.longshort AS > snapshot_longshort, snapshot.datetime AS snapshot_datetime, > snapshot.date AS snapshot_date, snapshot.year AS snapshot_year, > snapshot.month AS snapshot_month, snapshot.qty AS snapshot_qty, > snapshot.totalqty AS snapshot_totalqty, snapshot.price AS > snapshot_price, snapshot.possize AS snapshot_possize, snapshot.pnl AS > snapshot_pnl, snapshot.realized AS snapshot_realized, > snapshot.pnl_hedged AS snapshot_pnl_hedged, snapshot.is_stop AS > snapshot_is_stop, snapshot.stop_flag AS snapshot_stop_flag \nFROM > snapshot \nWHERE snapshot.totalqty = max(snapshot.totalqty)' [] > > > > --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
