On Nov 19, 2011, at 1:44 PM, Kelbethor wrote:
> I tried to improve usagerecipe for windowed range query (http://
> www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery) to
> include previous query and multiple column ordering support.
> I need to generate the following query:
>
I'm not sure how that query fits into the recipe there completely, but if it
helps this is an example of how to create the identical structure you picture
(with simplified column names):
from sqlalchemy.sql import func, table, column, select, and_
lac = table('lac',
column('a'),
column('b'),
column('c'),
column('d'),
)
L2 = select([lac.c.a,
lac.c.b,
lac.c.c,
lac.c.d,
func.row_number().over(order_by=[lac.c.a, lac.c.b, lac.c.c,
lac.c.d]).label('rownum')
]).where(lac.c.b==2).\
alias('L2')
L1 = lac.alias('L1')
stmt = select([L2]).select_from(
L2.join(L1, and_(
L2.c.a == L1.c.a,
L2.c.b == L1.c.b,
L2.c.c == L1.c.c,
L2.c.d== L1.c.d,
))
).where(
and_(
L2.c.rownum / 500 > 0,
L2.c.rownum/500<=1.0
)
)
print stmt
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
