"SELECT last_insert_rowid()" returns always "0"

Question

I have a problem with "last_insert_rowid()".

In my DB-Helper-Class I am doing the following:

public int getLastID() {
    final String MY_QUERY = "SELECT last_insert_rowid() FROM "+ DATABASE_TABLE5;
    Cursor cur = mDb.rawQuery(MY_QUERY, null);
    cur.moveToFirst();
    int ID = cur.getInt(0);
    cur.close();
    return ID;
}

But when I call this in my intent:

int ID = mDbHelper.getLastID(); 
Toast.makeText(this, "LastID: " + ID, Toast.LENGTH_SHORT).show();

I get always "0" back. That can not be, cause I have a lot of entries in my DB and the autoincrement value should be much higher.

What I am doing wrong?

Are you getting the row id immediately after your last insert? As I understand the function, it will only return a row id if you are using the same open connection as your last insert. — JohnP, Jul 02 '12 at 23:00
Oh, than I missunderstood the function. That could it be. What I wanted to do is get the highest ID in the table. Is there another function for this? — venni, Jul 02 '12 at 23:03

score 22 · Accepted Answer · answered Jul 02 '12 at 23:11

OK, that was the right hint John ;-)

The query should look like this:

public int getHighestID() {
final String MY_QUERY = "SELECT MAX(_id) FROM " + DATABASE_TABLE5;
Cursor cur = mDb.rawQuery(MY_QUERY, null);
cur.moveToFirst();
int ID = cur.getInt(0);
cur.close();
return ID;
}

score 13 · Answer 2 · answered Aug 26 '14 at 12:09

You don't need to put table name with last_insert_rowid() it will automatically select last inserted id from session. so you can just simply use this function as well.

public int getHighestID() {
    final String MY_QUERY = "SELECT last_insert_rowid()";
    Cursor cur = mDb.rawQuery(MY_QUERY, null);
    cur.moveToFirst();
    int ID = cur.getInt(0);
    cur.close();
    return ID;
}

"SELECT last_insert_rowid()" returns always "0"

2 Answers2

Linked