CODEWITHSUNDEEP
androidsqlsqliteandroid-contentprovider
Faheem Kalsekar
Faheem Kalsekar

Reputation: 1420

SQL Query to Delete Entries Sorted by Time

I have a table which has columns

_id = Primary Key Auto Increment

Title = String

timestamp = long

I have 35 entries in my table. My table can contain only 25 entries at any given time. So that means have of knock off 10 extra entries from my table.

Also 35 entries should be first sorted by timestamp and the last 10 entries should deleted so that i have just 25 recent entries.

Can some please help me with a delete query that first sorts the entries by timestamp and keeps only 25 entries, deleting the rest.

Upvotes: 0

Views: 306

Answers (3)

Germann Arlington
Germann Arlington

Reputation: 3353

Alternative to radashk method: You can delete one (oldest) record every time you insert a new record, it can be done in DB table trigger on insert:
DELETE FROM MYTABLE WHERE timestamp = MIN(timestamp);
this statement can be wrapped in record count check or something else to make sure that you maintain your minimum record count.

Upvotes: 0

Patrick Savalle
Patrick Savalle

Reputation: 4436

It sounds like you need a FIFO queue in SQL. A table that only stores the most recent 25 (or any other number of) items.

If so, then here is a solution:

http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql/

Upvotes: 0

Samson
Samson

Reputation: 2821

    DELETE FROM MYTABLE WHERE _id NOT IN 
   (SELECT _id from MYTABLE ORDER BY timestamp DESC LIMIT 25)

Keeps the latest 25 entries.

Upvotes: 3

Related Questions