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

by

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)

More Related Contents:

  1. Selecting more than one value
  2. Select first row in each GROUP BY group?
  3. Is it possible to insert multiple rows at a time in an SQLite database?
  4. How can I list the tables in a SQLite database file that was opened with ATTACH?
  5. SQLite Reset Primary Key Field
  6. Update table values from another table with the same user name
  7. How to pivot in SQLite or i.e. select in wide format a table stored in long format?
  8. How do I dump the data of some SQLite3 tables?
  9. SQLite – How do you join tables from different databases?
  10. Declare variable in SQLite and use it
  11. Does SQLite3 not support foreign key constraints?
  12. SQLite – ORDER BY RAND()
  13. Query to get records based on Radius in SQLite?
  14. How do I add a foreign key to an existing SQLite table?
  15. How to use SQL Order By statement to sort results case insensitive?
  16. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  17. sqlite alter table add MULTIPLE columns in a single statement
  18. Is there AUTO INCREMENT in SQLite?
  19. Get month from DATETIME in sqlite
  20. GROUP_CONCAT in SQLite
  21. How do I import .sql files into SQLite 3?
  22. How to create timestamp column with default value ‘now’?
  23. String concatenation does not work in SQLite
  24. Android multiple databases open
  25. Unfindable SQLite Syntax Error
  26. SQLite3 UNIQUE constraint failed error
  27. Does SQLite3 have prepared statements in Node.js?
  28. SQlite: select into?
  29. IF() statement alternative in SQLite
  30. Copy table structure to new table in sqlite3

Leave a Comment