title: Is it possible to use WHERE clause in same query as PARTITION BY?
body: <p>I need to write SQL that keeps only the minimum 5 records per each identifiable record in a table.  For this, I use <code>partition by</code> and delete all records where the value returned is greater than 5.  When I attempt to use the WHERE clause in the same query as the <code>partition by</code> statement, I get the error "Ordered Analytical Functions not allowed in WHERE Clause".  So, in order to get it to work, I have to use three subqueries.  My SQL looks ilke this:</p>
  
  <pre><code>delete mydb.mytable where (field1,field2) in
  (
      select field1,field2 from
      (
          select field1,field2,
          Rank() over
          (
              partition BY field1
              order by field1,field2
          ) n 
          from mydb.mytable
      ) x
      where n &gt; 5
  )
  </code></pre>
  
  <p>The innermost subquery just returns the raw data.  Since I can't use WHERE there, I wrapped it with a subquery, the purpose of which is to 1) use WHERE to get records greater than 5 in rank and 2) select only field1 and field2.  The reason why I select only those two fields is so that I can use the IN statement for deleting those records in the outermost query.</p>
  
  <p>It works, but it appears a bit cumbersome.  I'd like to consolidate the inner two subqueries into a single subquery.  Is this possible?</p>