Order of insert in android database

Question

I am wondering how can I insert an element at the beginning of the data base? I want to do this because: a) I want to display the elements (ListView) as 'last-inserted on top and first-inserted on the bottom' (like a stack) b) I want to limit the amount of elements in my db. When a new element is added (over the limit) I can put the new one (at the beginning) and delete the last one. (Don't know yet how to delete the last element).

I was searching for a solution but I am starting to wonder I have any control of how the element are inserted. If not I was thinking about displaying the database from end to bottom. But don't actually know how to do it since the cursor is always set at the beginning of the db. If could achieve this I can solve b) by deleting the first element (again don't know how to achieve this yet).

Answer 1

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid"

To get last inserted at top and first inserted at bottom....

SELECT * from mytable order by ROWID desc;

Read more about ROWID here

And, to delete the oldest:

DELETE from mytable WHERE ROWID = (SELECT MIN(ROWID) FROM mytable);

If you use the ROWID, you do not have to modify your existing table. And, the value of ROWID is guaranteed.

Answer 2

You can store a timestamp (as a new field) when you insert a record into your database. Then you delete the oldest record you can find if it is over the limit (e.g. using MAX SQL keyword). You can display the records in reverse-chronological order by sorting DESC with your timestamp field.