title: sqlite equivalent of row_number() over ( partition by ...?
body: <p>I'd like to know if it's possible to do the following using a single sqlite statement:</p>
  <p>My table looks something like this:</p>
  <pre><code>|AnId|UserId|SomeDate|SomeData|
  |123 |A     |1/1/2010|aadsljvs|
  | 87 |A     |2/9/2010|asda fas|
  |193 |A     |2/4/2010|aadsljvs|
  |927 |A     |7/3/2010|aadsasdf|
  |816 |B     |1/1/2010|aa32973v|
  |109 |B     |7/5/2010|aaasfd10|
  | 39 |B     |1/3/2010|66699327|
  ...
  </code></pre>
  <p>Each row has a unique id, a user id, a datetime value, and some other data.</p>
  <p>I'd like to delete records so I keep the latest 10 records <strong>per user</strong>, based on SomeDate.</p>
  <p>In sql server I'd use something like this:</p>
  <pre><code>delete d
  from data d
  inner join (
      select UserId
          ,  AnId
          ,  row_number() over ( partition by UserId order by SomeDate desc ) 
                as RowNum
      from data 
  ) ranked on d.AnId = ranked.AnId
  where ranked.RowNum &gt; 10
  </code></pre>
  <p>Is there a way to do this in sqlite? The edge case where there are several records with the same SomeDate isn't a particular worry, e.g. if I keep all those records that'd be fine.</p>