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 )
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.
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)"; 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
