title: Using window functions to LIMIT a query with SqlAlchemy on Postgres
body: <p>I'm trying to write the following sql query with sqlalchemy ORM:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM
     (SELECT *, row_number() OVER(w)
      FROM (select distinct on (grandma_id, author_id) * from contents) as c
      WINDOW w AS (PARTITION BY grandma_id ORDER BY RANDOM())) AS v1
  WHERE row_number &lt;= 4;
  </code></pre>
  
  <p>This is what I've done so far:</p>
  
  <pre><code>s = Session()
  
  unique_users_contents = (s.query(Content).distinct(Content.grandma_id,
                                                    Content.author_id)
                           .subquery())
  
  windowed_contents = (s.query(Content,
                               func.row_number()
                               .over(partition_by=Content.grandma_id,
                                     order_by=func.random()))
                       .select_from(unique_users_contents)).subquery()
  
  contents = (s.query(Content).select_from(windowed_contents)
              .filter(row_number &gt;= 4)) ##  how can I reference the row_number() value?
  
  result = contents
  for content in result:
      print "%s\t%s\t%s" % (content.id, content.grandma_id,
                            content.author_id)
  </code></pre>
  
  <p>As you can see it's pretty much modeled, but I have no idea how to reference the <code>row_number()</code> result of the subquery from the outer query where. I tried something like <code>windowed_contents.c.row_number</code> and adding a <code>label()</code> call on the window func but it's not working, couldn't find any similar example in the official docs or in stackoverflow.</p>
  
  <p>How can this be accomplished? And also, could you suggest a better way to do this query?</p>