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.

score 21 · Accepted Answer · answered Jun 12 '13 at 16:17

21

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)

answered Jun 12 '13 at 16:17

Jett

326
2
5

Brilliant! And know I know about `OFFSET`. – Craig Silver Mar 17 '18 at 00:18
I dont see why this is the answer to the original question. He wants to delete the old entries of ALL users and not only for one. – Carsten Aug 10 '19 at 13:47
@Carsten @Jett - It's so long ago I can't remember testing this ... does it even work having an `IN` clause that's correlated to the `data` table? If so then I guess the `IN` gets evaluated separately for each row and therefore it should work for ALL users. But certainly Jett's description isn't what I wanted, i.e. it's not delete records for a **given UserId** but rather for all UserIds. That's what makes it tricky and in SQL Server needs `row_number() over ( partition by ... )` – Rory Aug 18 '21 at 11:21

score 2 · Answer 2 · answered Nov 10 '17 at 08:52

I needed to fetch the second row for each "object" in a table with a 1 to many relationship to the "object" table.

Usually in SQL this will be done using ROW_NUMBER() OVER(PARTITION BY object_id ORDER BY primary_id DESC)

In Sqlite I had to come up with this voodoo sub-query to get the same result

SELECT object_id, MAX(_id)
FROM (SELECT object_id, _id
FROM aTable
EXCEPT
SELECT object_id, MAX(_id)
FROM aTable
GROUP BY object_id)
GROUP BY object_id;

Note: The _id is the primary key of aTable and the object table has a 1 to many relationship with the queried table

score 1 · Answer 3 · edited Jan 12 '17 at 15:48

1

If you already haven't got the answer. If it's one table, then you don't need any joins. You can just use:

Delete From data
where AnId not in (Select AnId
                   from data
                   Order by SomeDate DESC
                   Limit 10)

edited Jan 12 '17 at 15:48

Olaf Dietsche

72,253
8
102
198

answered Feb 11 '16 at 01:38

Jawad Khan

21
4

score 1 · Answer 4 · answered Sep 18 '18 at 12:43

1

As of Sqlite 3.25 window functions are supported. See https://www.sqlite.org/windowfunctions.html for details.

answered Sep 18 '18 at 12:43

Ralf Bertling

29
2

score 0 · Answer 5 · answered Nov 02 '10 at 02:38

0

This might be prohibitively expensive (perhaps only do it when a user inserts a new record?) but how about this:

for user in users:
  user-records = select * from records where user=user
  if user-records.length > 10:
    delete from records where user=user and date<user-records[10]

(in a mix of SQL and pseudocode)

answered Nov 02 '10 at 02:38

sbirch

871
1
10
18

Sure, I can do it procedurally, but I want a way to do it in a single statement if possible. If doing it procedurally it would also be possible to use a single statement per user, and only for the users with records in the table – Rory Nov 02 '10 at 09:42
Ah, yeah; I have no idea then. Out of curiosity, how can you do it in a single statement per-user? – sbirch Nov 03 '10 at 19:42
Well, that's the question! the syntax in the question shows how to do it in sql server. I don't know if this is possible in sqlite. I suspect not. – Rory Nov 17 '10 at 15:55

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

5 Answers5

Linked