title: Prune table to 500 records in JPQL
body: <p>I work on some kind of cache and from time to time, we need to prune the table to 500 records, based on a <code>last_access_date</code> (only keep the 500 recently accessed rows).</p>
  
  <p>With "plain" SQL, this could be done with:</p>
  
  <pre><code>DELETE FROM records WHERE id not in 
      (SELECT id FROM records ORDER BY last_access_date DESC LIMIT 500)
  </code></pre>
  
  <p>Now as there is no <code>LIMIT</code> or something like <code>ROWNUM</code> in JPQL, the only solution I found was in native SQL, which is suboptimal, because we run on multiple DBMS (at least Oracle and MSSQL).</p>
  
  <p>Also, <a href="https://docs.oracle.com/javaee/6/api/javax/persistence/Query.html#setMaxResults(int)" rel="noreferrer"><code>setMaxResults()</code></a> (JPQLs version of <code>LIMIT</code>) doesn't seem valid for <code>DELETE</code> statements.</p>
  
  <p>Is there really no way to to this with JPQL?</p>