Hello. I have already solved the issue by using subquery:
SELECT t.id AS t_id, t.rownum AS t_rownum FROM ( SELECT FROM foo.id AS id, row_number() OVER (ORDER BY foo.id) AS rownum ) AS t WHERE rownum % 50 = 1 I have just tried your suggestion about using HAVING instead of WHERE, but that fails with the same error. Thus a label cannot be used inside a query. However, I am still curious whether the original WindowedRangeQuery recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery works or also has this error. Thank you, Ladislav Lenart On 6.6.2013 18:26, Charlie Clark wrote: > Am 06.06.2013, 16:30 Uhr, schrieb Ladislav Lenart <lenart...@volny.cz>: > >> The returned query q produces the following SQL: >> SELECT >> foo.id AS foo_id, >> row_number() OVER (ORDER BY foo.id) AS rownum >> FROM foo >> WHERE rownum % 2 = 1 >> When executed, it generates the following error: >> sqlalchemy.exc.ProgrammingError: >> (ProgrammingError) column "rownum" does not exist > > Just a guess but the error is coming from Postgres. You'll need to > doublecheck the docs but I don't think "rownum" is available for the WHERE > clause because OVER works like the aggregate functions. You can either try > wrapping the SELECT in correlated subquery as the example shows or simply > use HAVING rownum % 2 = 1 (I think this is best approach but I'm far from > an expert). Try the SQL directly in the DB to see which works. > > Charlie -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.