So I had a query in raw PostgreSQL which was trying to find the start and end of an event in a history table:
select jh.job_id, jh.created as start_t, jh.status, min(jh.created) over (PARTITION by jh.job_id ORDER BY jh.created ROWS BETWEEN 1 FOLLOWING and 3 FOLLOWING ) as end_t from job_histories jh, jobs j, tasks t where jh.status in ('running: copying files in', 'running','Running') and jh.job_id = j.job_id and j.task_id = t.task_id and t.campaign_id = 704 When trying to render this in SQLAlchemy, as in dbhandle.query(JobHistory.job_id.label('job_id'), JobHistory.created.label('start_t'), JobHistory.status.label('status'), func.max(JobHistory.created).over(partition_by = JobHistory.job_id, order_by=JobHistory.created, rows=(1,3) ).label('end_t')) .join(Job) .join(Task) .filter(JobHistory.status.in_([copy_start_status,'running','Running'])) .filter(JobHistory.job_id == Job.job_id) .filter(Job.task_id == Task.task_id) .filter(Task.campaign_id == campaign_id) I find I cannot ask for "BETWEEN 1 FOLLOWING and 3 FOLLOWING", becuase the code insists the first part of the range must be none, zero or negative, and the second part must be none, zero, or positive. -- thus I seem to always have to give a range that spans the current item -- the start of the range has to always be before the current row (or equal to it) and the end of the range has to always be at or after the current row. Why the sign restrictions?!? -- 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.