How to Get Last Insert Id in Sqlite

How to retrieve the last autoincremented ID from a SQLite table?

With SQL Server you'd SELECT SCOPE_IDENTITY() to get the last identity value for the current process.

With SQlite, it looks like for an autoincrement you would do

SELECT last_insert_rowid()

immediately after your insert.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09429.html

In answer to your comment to get this value you would want to use SQL or OleDb code like:

using (SqlConnection conn = new SqlConnection(connString))
{
    string sql = "SELECT last_insert_rowid()";
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    int lastID = (Int32) cmd.ExecuteScalar();
}

How to get last insert Id in SQLite?

SQLite

This is available using the SQLite last_insert_rowid() function:

The last_insert_rowid() function returns the ROWID of the last row
insert from the database connection which invoked the function. The
last_insert_rowid() SQL function is a wrapper around the
sqlite3_last_insert_rowid() C/C++ interface function.

PHP

The PHP version/binding of this function is sqlite_last_insert_rowid():

Returns the rowid of the row that was most recently inserted into the
database dbhandle, if it was created as an auto-increment field.

How to get the last inserted id in jdbc with SQLite3?

The function last_insert_rowid() returns:

the ROWID of the last row insert from the database connection which
invoked the function

So, if you closed the connection with which you did the last insertion and run the query with another connection you will not get the rowid of the last row inserted.

Also if there are multiple tables in your database and you do insertions in more than 1 of them with the same connection, you will have to call last_insert_rowid() after the last insertion in each table to get the rowid of the last row inserted in each table.

When you use MAX(id) in a query and id is defined as INTEGER PRIMARY KEY you get the max id of the table and not necessarily the rowid of the last row inserted, because:

If you ever delete rows or if you ever create a row with the maximum
possible ROWID, then ROWIDs from previously deleted rows might be
reused when creating new rows and newly created ROWIDs might not be in
strictly ascending order.

The only way to be sure that MAX(id) will return the rowid of the last row inserted is if you have defined id with the keyword AUTOINCREMENT also:

id INTEGER PRIMARY KEY AUTOINCREMENT

because in this case:

The ROWID chosen for the new row is at least one larger than the
largest ROWID that has ever before existed in that same table. If the
table has never before contained any data, then a ROWID of 1 is used.

Finally, it is common practice to refer to columns by their names or aliases, so instead of:

SELECT MAX(id) FROM temp

you should use:

SELECT MAX(id) AS max_id FROM temp

so that you can access the result of the query by the alias max_id:

final Integer id = rs.getInt("max_id");

How to get last inserted row in SQlite android

Try this code

String selectQuery = "SELECT  * FROM " + "sqlite_sequence";
Cursor cursor = db.rawQuery(selectQuery, null);
cursor.moveToLast();

or

SELECT * 
FROM    TABLE
WHERE   ID = (SELECT MAX(ID)  FROM TABLE);

or

SELECT * FROM table ORDER BY column DESC LIMIT 1;

Best way to get the ID of the last inserted row on SQLite

If you can guarantee that your ID column is an auto-increment column, MAX(ID) is fine.

But to cover any case, there's a specialized SQLite function called LAST_INSERT_ROWID():

SELECT LAST_INSERT_ROWID();

In FMDB, you use the -lastInsertRowId method (which internally runs the above):

int lastId = [fmdb lastInsertRowId];

How to get the ID of the last record inserted in SQLite with Delphi 10?

You could query last_insert_rowid if your ID column is declared as INTEGER PRIMARY KEY. In such case the column becomes alias for the ROWID. If that is your case, you can query it natively e.g. this way:

uses
  FireDAC.Phys.SQLiteWrapper;

function GetLastInsertRowID(Connection: TFDConnection): Int64;
begin
  Result := Int64((TObject(Connection.CliObj) as TSQLiteDatabase).LastInsertRowid);
end;

Or in common way by calling GetLastAutoGenValue method:

function GetLastInsertRowID(Connection: TFDConnection): Int64;
begin
  Result := Int64(Connection.GetLastAutoGenValue(''));
end;

Get last inserted value from sqlite database Android

Well actually the SQLiteDatabase class has its own insert method which returns the id of the newly created row. I think this is the best way to get the new ID.
You can check its documentation here.

I hope this helps.

---

Related Topics