keep last 10 and delete all other rows - sqlite

Question

I need to execute this below query.

db.delete(TABLE_SESSIONS, selectQuery, null);

The Select Query should be those except last 10 entries.

How to achieve this?

String selectQuery = "SELECT  * FROM " + TABLE_SESSIONS +" WHERE " +KEY_SESSION_ID+ " IN (SELECT "+KEY_SESSION_ID + " FROM "+TABLE_SESSIONS+" WHERE " +KEY_SESSION_ID+ " ORDER BY Col LIMIT 10)";

Can anyone improve my query? ( I'm not good with query )

Refer this [sql-query-delete-all-records-from-the-table-except-latest-n](http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n) — Chirag Ghori, Feb 15 '14 at 09:49

score 4 · Answer 1 · answered Feb 15 '14 at 11:48

SQLiteDatabase delete() takes in a WHERE expression and not a SELECT statement.

If your selection returns those rows you want to keep:

String selectQuery = "SELECT  * FROM " + TABLE_SESSIONS +" WHERE " +KEY_SESSION_ID+ " IN (SELECT "+KEY_SESSION_ID + " FROM "+TABLE_SESSIONS+" WHERE " +KEY_SESSION_ID+ " ORDER BY Col LIMIT 10)"

... you can use this in the delete using NOT IN like this:

db.delete(TABLE_SESSIONS,
    "ROWID NOT IN (SELECT ROWID FROM " + TABLE_SESSIONS + " ORDER BY Col LIMIT 10)",
    null);

The ROWID is an alias for the INTEGER PRIMARY KEY of a table; the WHERE condition you had in your subselect is not necessary as any non-zero ID gets selected.

score 3 · Accepted Answer · answered Feb 15 '14 at 09:39

3

try following query.

String selectQuery = "SELECT  * FROM " + TABLE_SESSIONS +" WHERE " +KEY_SESSION_ID+ " IN (SELECT "+KEY_SESSION_ID + " FROM "+TABLE_SESSIONS+" WHERE " +KEY_SESSION_ID+ " ORDER BY Col DESC LIMIT 10)";

answered Feb 15 '14 at 09:39

King-of-IT

578
4
18

score 2 · Answer 3 · answered Feb 15 '14 at 09:37

SELECT * FROM TABLE_SESSIONS WHERE YOUR_SORT_FIELD NOT IN (select TOP 10 YOUR_SORT_FIELD from TABLE_SESSIONS order by YOUR_SORT_FIELD DESC)

DESC and ASC can be changed if you need last ten or latest 10

and YOUR_SORT_FIELD is the field that identifies the latest data

keep last 10 and delete all other rows - sqlite

3 Answers3