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?
5
-
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– njzk22012-12-10 08:42:23 +00:00Commented Dec 10, 2012 at 8:42
-
Create a trigger when you update to order it and delete the last additions?adarsh– adarsh2012-12-10 08:47:46 +00:00Commented Dec 10, 2012 at 8: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– Tooroop2012-12-10 08:47:47 +00:00Commented Dec 10, 2012 at 8:47
-
this post may help you stackoverflow.com/questions/12162549/…Aerrow– Aerrow2012-12-10 08:49:08 +00:00Commented Dec 10, 2012 at 8: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– Tooroop2012-12-10 08:50:24 +00:00Commented Dec 10, 2012 at 8:50
Add a comment
|
3 Answers
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);
1 Comment
Tooroop
hmm...yeah I think that this could also be a possible solution
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;
Comments
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.
2 Comments
Daniel1979
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 :)
Tooroop
oh, and how can I do this in one call?