Android SQLite select and delete from row number

Question

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?

row number depends on order by. You would need a "insert date" column and base your queries on that. (please note that this is irrelevant to android and sqlite. most sql engine will work the same way) — njzk2, Dec 10 '12 at 08:42
Create a trigger when you update to order it and delete the last additions? — adarsh, Dec 10 '12 at 08:47
The order has to be descending. And then I should select all rows from 20 to the end(The end I would get by getting the count of rows). But also note that 20 should not be the id, it is really the number of the row in the table. I don't thik i can base my queries on a date. — Tooroop, Dec 10 '12 at 08:47
this post may help you http://stackoverflow.com/questions/12162549/delete-sqlite-row-with-where-clause-with-multiple-clauses — Aerrow, Dec 10 '12 at 08:49
I'm now doing this the way that everytime I get an update i delete the first row. So I'm constantly having 20 newest rows in the table, but I don't think that this is the best solution. — Tooroop, Dec 10 '12 at 08:50

score 2 · Answer 1 · edited Dec 10 '12 at 11:53

2

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

edited Dec 10 '12 at 11:53

CL.

173,858
17
217
259

answered Dec 10 '12 at 10:49

JustDanyul

13,813
7
53
71

hmm...yeah I think that this could also be a possible solution – Tooroop Dec 10 '12 at 11:03

Dan · Answer 2 · 2013-08-23T23:57:36.157

2

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;

edited Aug 23 '13 at 23:57

answered Mar 05 '13 at 20:48

Dan

2,625
7
39
52

score 0 · Accepted Answer · answered Dec 10 '12 at 10:38

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.

You are on the right track with LIMIT and OFFSET, but your implementation is very inefficient, this can be done in one delete call. Solution you show here first loads and iterates over a cursor, and then fires a "delete from" as many times as needed. It can be done hitting the database just once :) — JustDanyul, Dec 10 '12 at 10:55

Android SQLite select and delete from row number

3 Answers3