On Thu, May 3, 2018 at 6:05 PM, Björn Nadrowski <bjrnfrd...@gmail.com> wrote: > > Thanks for your reply. I guessed that this should be possible using alias > and functions. But I was unable to write a simple script that achieves that. > Could you help me with that? > Or do you want me to show the things that I tried but do not work?
no need, can you try this query? T2 = aliased(CTestTable, name="T2") T1 = aliased(CTestTable, name="T1") max_subq = session.query( func.max(T2.datetime) ).filter(T2.info == T1.info).filter( T2.datetime < T1.datetime).correlate(T1).label("PreviousDate") subq = session.query( T1.id, T1.datetime, T1.val, T1.info, max_subq).subquery("T") q = session.query( subq.c.id, subq.c.datetime, subq.c.val, subq.c.info, ( func.julianday("datetime") - func.julianday("PreviousDate")).label("datediff") ).select_from( subq ) q.all() log output is: SELECT "T".id AS "T_id", "T".datetime AS "T_datetime", "T".val AS "T_val", "T".info AS "T_info", julianday(?) - julianday(?) AS datediff FROM (SELECT "T1".id AS id, "T1".datetime AS datetime, "T1".val AS val, "T1".info AS info, (SELECT max("T2".datetime) AS max_1 FROM "testTable" AS "T2" WHERE "T2".info = "T1".info AND "T2".datetime < "T1".datetime) AS "PreviousDate" FROM "testTable" AS "T1") AS "T" 2018-05-03 20:11:30,082 INFO sqlalchemy.engine.base.Engine ('datetime', 'PreviousDate') is that right? note the uppercase names need to be quoted, literal values are turned into bind parameters, etc. > > Thanks, Bjoern > > > Am Montag, 23. April 2018 14:37:43 UTC+2 schrieb Mike Bayer: >> >> We can help you with this query but I'd first ask that you have >> reviewed the documentation that covers these patterns, within the Core >> tutorial: http://docs.sqlalchemy.org/en/latest/core/tutorial.html >> specifically, how to do aliased subqueries >> (http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-aliases), >> functions >> (http://docs.sqlalchemy.org/en/latest/core/tutorial.html#functions), >> etc. >> >> > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.