I am trying to get the last inserted rowid from a sqlite database in Android. I have read a lot of posts about it, but can't get one to work. This is my method: public Cursor getLastId() { return mDb.query(DATABASE_TABLE, new String[] {KEY_WID}, KEY_WID + "=" + MAX(_id), null, null, null, null, null);} I have tried with MAX, but I must be using it wrong. Is there another way?
A:
I am an SQL noob but in this situation what I have been doing is sending them back with an ORDER BY KEY_WID and just use the first one in the list.
schwiz
2010-10-25 19:39:20
I am such a SQL noob that I don't even get how to use the ORDER BY. But as always when i post a question, i find the answer and all of a sudden it seems really obvious. I used your idea of getting all id's and just parse through them. For that I set the cursor to start with the last id: if (cursor.moveToLast()) { id.add(cursor.getString(0));} Thanks for your help!
Anna-Karin
2010-10-25 20:27:01
this flowchart may come in handy http://www.sqlite.org/lang_select.html
schwiz
2010-10-26 00:54:58
This is unreliable as a new value may have been inserted between the insert and the 'order by' query, and so could give you the wrong value. SELECT last_insert_rowid() is better because it gives the inserted id of the last inserted id *on the same connection*, and so will always return the right id.
miket2e
2010-10-29 08:34:50
A:
Use
SELECT last_insert_rowid();
to get the last inserted rowid.
If you are using AUTOINCREMENT keyword then
SELECT * from SQLITE_SEQUENCE;
will tell you the values for every table.
dtmilano
2010-10-25 20:30:35
I have a class DataHelper where I have all methods that deal with the database. I have seem these methods above like this in many forums and the documentation, but I don't understand how they are used. Do you use them inside the DataHelper class, or inside the actual Sqlite from the terminal? Sorry if i am vague, I am just trying to figure this out!
Anna-Karin
2010-10-25 20:35:32
you can do a rawQuery with the Android SQLiteDatabase and feed it the string "Select * from SQLITE_SEQUENCE"
schwiz
2010-10-26 00:53:51