CODEWITHSUNDEEP
androidsqlitenumbersrow
Tooroop
Tooroop

Reputation: 1884

Android SQLite select and delete from row number

I'm having a database where I am constantly putting new data that I get from a service. This is working in a thread, so everytime I see new data I put ii in the database. I am only showing the freshest 20 news in my app, and that is working perfectly. The problem is that I want to delete all the oldest notification from the database, and that only the freshest 20 stay. I don't know how to write that query. Any help?

Upvotes: 0

Views: 1885

Answers (3)

Dan
Dan

Reputation: 2755

I like @JustDanyul's answer but I don't think you need the IN clause in this case. It looks like he's using 1 table so couldn't you write the query like this?

DELETE FROM tableName ORDER BY id DESC LIMIT -1 OFFSET 20;

Upvotes: 2

JustDanyul
JustDanyul

Reputation: 14044

According to the documentation to SQLite you have both ORDER BY and LIMIT support in "DELETE FROM".

http://www.sqlite.org/lang_delete.html

However, I haven't tried this, so I can not say if it work on SQLite under android. However, what DOES work (I done this in the past) is using 

DELETE FROM tableName 
WHERE myid IN (SELECT id FROM mytable ORDER BY id LIMIT -1 OFFSET 20);

Upvotes: 2

Tooroop
Tooroop

Reputation: 1884

Well in the end I'm here to answer my own question :)

The thing that i done is this:

if(fetchNotificationsCount() > 20){
        String sql = "SELECT * FROM (SELECT * FROM " + PPASQLiteHelper.TABLE_NOTIFICATIONS + " ORDER BY " + PPASQLiteHelper.COLUMN_ID + " DESC) LIMIT " + fetchNotificationsCount() + " OFFSET 20";
        Cursor cursor = database.rawQuery(sql, null);
        cursor.moveToFirst();
        while(!cursor.isAfterLast()){
            database.delete(PPASQLiteHelper.TABLE_NOTIFICATIONS, PPASQLiteHelper.COLUMN_ID + "=" + cursor.getLong(0), null);
            cursor.moveToNext();
        }
        cursor.close();
    }

With the LIMIT and OFFSET i was able to do what I intended. Thanks for the help anyway.

Upvotes: 0

Related Questions