why would you use order by with max ? id say thats an invalid
request to start with.
On Jul 21, 2006, at 2:33 AM, Randall Smith wrote:
> Calling min or max on a SelectResults instance that has ordering,
> generates invalid SQL. The attached example runs on SQLite, but
> will not run on Postgresql. Here is the query it generates:
>
> SELECT max(t3.t1_id)
> FROM (SELECT t1.id AS t1_id, t1.name AS t1_name, t2.id AS t2_id,
> t2.id AS t2_name
> FROM t1, t2
> WHERE t1.id = t2.t1_id) AS t3 ORDER BY t3.t1_id ASC, t3.t2_name
> DESC, t3.t1_name ASC 'SELECT max(t3.t1_id) \nFROM (SELECT t1.id AS
> t1_id, t1.name AS t1_name, t2.id AS t2_id, t2.id AS t2_name \nFROM
> t1, t2 \nWHERE t1.id = t2.t1_id) AS t3 ORDER BY t3.t1_id ASC,
> t3.t2_name DESC, t3.t1_name ASC' {}
>
>
> and here is the error:
>
> raise exceptions.SQLError(statement, parameters, e)
> sqlalchemy.exceptions.SQLError: (ProgrammingError) ERROR: column
> "t3.t1_id" must appear in the GROUP BY clause or be used in an
> aggregate function
>
> Sorry that the test case is more complicated than necessary. I was
> duplicating my code trying to find the error's cause.
>
> Randall
> """Delete fails after access to a lazy attribute.
>
> """
> from sqlalchemy import *
> from sqlalchemy.ext.selectresults import SelectResults
>
> metadata = DynamicMetaData(name="test")
>
> t1 = Table('t1', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', String)
> )
>
> t2 = Table('t2', metadata,
> Column('id', Integer, primary_key=True),
> Column('t1_id', Integer, ForeignKey(t1.c.id)),
> Column('name', String)
> )
>
> t3 = select([t1.c.id.label('t1_id'), t1.c.name.label('t1_name'),
> t2.c.id.label('t2_id'), t2.c.id.label('t2_name'),
> ],
> t1.c.id == t2.c.t1_id
> ).alias('t3')
>
> class T1(object):
> pass
>
> class T2(object):
> pass
>
> class T3(object):
> pass
>
> mapper(T1, t1,
> )
> mapper(T2, t2,
> properties={'t1':relation(T1, backref=backref('t2s',
> private=True))}
> )
> mapper(T3, t3)
>
> engine = create_engine("sqlite:///:memory:")
> engine = create_engine("postgres:///test")
> engine.echo = True
> metadata.connect(engine)
> metadata.create_all()
>
> # Populate tables.
> session = create_session(bind_to=engine)
> for i in range(10):
> t1obj = T1()
> t1obj.name = 'test'
> session.save(t1obj)
> for i in range(5):
> t2obj = T2()
> t2obj.name = 'test'
> t1obj.t2s.append(t2obj)
> session.flush()
> session.close()
>
> # SelectResults
> session = create_session(bind_to=engine)
> query = session.query(T3)
> sr = SelectResults(query)
> ordering = [asc(T3.c.t1_id), desc(T3.c.t2_id), asc(T3.c.t1_name)]
> sr = sr.order_by(ordering)
> print sr.max(T3.c.t1_id)
> session.flush()
> session.close()
> ----------------------------------------------------------------------
> ---
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to
> share your
> opinions on IT & business topics through brief surveys -- and earn
> cash
> http://www.techsay.com/default.php?
> page=join.php&p=sourceforge&CID=DEVDEV________________________________
> _______________
> Sqlalchemy-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
