SQLite last_insert_rowid() Function

The SQLite last_insert_rowid() function returns the ROWID of the last row inserted into the database from the connection currently calling this function.

ROWID is a unique identifier for a row in a table, and it is a 64-bit integer value. You can use case-insensitive rowid, oid or _rowid_ get the ROWID of the row. Unless the table has a column with the above name explicitly defined.

If a table has a INTEGER PRIMARY KEY column, the column name becomes an alias for ROWID.

Syntax

Here is the syntax of the SQLite last_insert_rowid() function:

last_insert_rowid()

Parameters

The SQLite changes() function does not require any parameters.

Return value

The SQLite last_insert_rowid() function returns the ROWID of the last row found in the database from the current connection, which is a 64-bit integer value.

Examples

Implicit ROWID column

To demonstrate what the SQLite last_insert_rowid() function can do, we create a table test_last_insert_rowid with the following statement:

CREATE TABLE test_last_insert_rowid (
    txt VARCHAR(100)
);

Let us insert 2 rows using the following statement,

INSERT INTO test_last_insert_rowid
VALUES ('Hello'), ('World');

Let’s look at the rows in the table using the SELECT statement:

SELECT * FROM test_last_insert_rowid;

txt
-----
Hello
World

Since each table has a default column named rowid, we can display the rowid column :

SELECT rowid, * FROM test_last_insert_rowid;

rowid  txt
-----  -----
1      Hello
2      World

Let’s get the latest ROWID using the SQLite last_insert_rowid() function:

SELECT last_insert_rowid();

last_insert_rowid()
-------------------
2

This is the same result we saw above.

Explicit ROWID column

If a table has a INTEGER PRIMARY KEY column , the column becomes an alias for ROWID. For example, we use the following statement to create a table named test_last_insert_rowid_2:

CREATE TABLE test_last_insert_rowid_2 (
    id INTEGER PRIMARY KEY,
    txt VARCHAR(100)
);

Here, we have used the id column as the primary key. As described above, id is an alias for rowid.

Let’s insert two rows into the table:

INSERT INTO test_last_insert_rowid_2 (txt)
VALUES ('Hello'), ('World');

To view rows in the table and the ROWID of each row, use the following statement:

SELECT rowid, * FROM test_last_insert_rowid_2;

id  id  txt
--  --  -----
1   1   Hello
2   2   World

Here, in the result returned, the rowid column aliases id.

You can insert a custom id as following:

INSERT INTO test_last_insert_rowid_2 (id, txt)
VALUES (123, 'Hello');

To see the ROWID of the last inserted row, use the following SELECT statement with the SQLite last_insert_rowid() function:

SELECT last_insert_rowid();

last_insert_rowid()
-------------------
123

It’s exactly the value we just used in the INSERT statement.