sqlite equivalent of row_number() over ( partition by ...?

Question

I'd like to know if it's possible to do the following using a single sqlite statement:

My table looks something like this:

|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| ... 

Each row has a unique id, a user id, a datetime value, and some other data.

I'd like to delete records so I keep the latest 10 records per user, based on SomeDate.

In sql server I'd use something like this:

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 > 10 

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.

Answer 1

I know this question is old, but the following SQLite statement will do what Rory was originally asking for in one statement - Delete all records for a given UserId that are not the 10 most recent records for that UserId (based on SomeDate).

DELETE FROM data WHERE AnId IN (SELECT AnId                FROM data AS d                WHERE d.UserId = data.UserId                ORDER BY SomeDate DESC                LIMIT -1 OFFSET 10)