How to delete records if more than 20

Question

The fact is that I need to delete if there are more than 20 records and at the same time only old ones, that is, by date. I found a SQL query 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 it is completely incomprehensible. I can delete if more than 20, but the problem is that there are 2 checks, and I'm new to SQLite, a mess in my head.

I have the following columns:

 private static final String TABLE_SEARCH = "search_table"; private static final String KEY_ID = "id"; private static final String KEY_DATE = "date";

As in my case, use the query above. And how to remake db.delete ()?

if the number of records is more than 20 then delete by date ..

Accepted Answer · 2016-07-23T09:39:51

 DELETE FROM search_table WHERE date NOT IN ( SELECT date FROM search_table ORDER BY date desc LIMIT 20 )

He wrote on his knee, possible typos, but the idea is this.
By internal request, we select everything that needs to be left, that is, the top 20 records by date, and by external - we delete everything, except for the fact that I chose the internal one.

Here's another option, this one is better:

 DELETE FROM search_table WHERE id < SELECT id from ( SELECT id, date FROM search_table ORDER BY date desc LIMIT 1 OFFSET 19 )

Yes, there may be a problem if the dates coincide completely
String deleteQuery = "DELETE FROM search_table WHERE id <SELECT id from (SELECT id, date FROM search_table ORDER BY date desc LIMIT 1 OFFSET 19)";

How to delete records if more than 20

1 answer 1

More articles: