How to remove records if more than 20
The thing is, I need to delete if the records are more than 20 and only the old ones, that's the date. I found a SQL request somewhere.
DELETE tt FROM ( SELECT DISTINCT uid --- these 3 lines can be replaced FROM tableX --- with: UserTable AS du ) AS du --- a table that you probably have JOIN tableX AS tt ON tt.uid = du.uid AND tt.timestampColumn > ( SELECT timestampColumn AS ts FROM tableX WHERE uid = du.uid ORDER BY ts ASC LIMIT 1 OFFSET 199 )
But that's not clear. I can remove more than 20, but the problem is, two checks, and I'm in the SQLite newcomers, a pot in the head.
I have these poles:
private static final String TABLE_SEARCH = "search_table"; private static final String KEY_ID = "id"; private static final String KEY_DATE = "date";
How do I use the request above? And how do you redo db.delete?
DELETE FROM search_table WHERE date NOT IN ( SELECT date FROM search_table ORDER BY date desc LIMIT 20 )
He was on his knee, there might be fingerprints, but that's the idea.
Internally, we choose everything that needs to be left, i.e. a top of 20 records on the date, and externally, we remove everything, except that we chose the inside.
Here's another option, this better one:
DELETE FROM search_table
WHERE id < SELECT id from (
SELECT id, date
ORDER BY date desc
LIMIT 1 OFFSET 19