SQLite has a function called last_insert_rowid() that returns the ROWID of the last row insert from the database connection which invoked the function.

Example

Here’s an example to demonstrate how the last_insert_rowid() function works in SQLite.

First, let’s create a table and insert some data:

CREATE TABLE Cats( 
    CatId INTEGER PRIMARY KEY, 
    CatName
);

INSERT INTO Cats VALUES 
    ( NULL, 'Brush' ),
    ( NULL, 'Scarcat' ),
    ( NULL, 'Flutter' );

This will automatically create a ROWID for each row. Therefore, this SQL statement would have created three different ROWIDs; 1, 2, and 3.

We can now use the last_insert_rowid() function to return the value of the last ROWID.

SELECT last_insert_rowid();

Result:

3

As expected, the last ROWID is 3.

Insert into a Different Table

Note that last_insert_rowid() works based on database connection, not at the table level.

Therefore, if I create a new table, then insert rows into that table, the last_insert_rowid() value will be based on that insert operation.

CREATE TABLE Dogs( 
    DogId INTEGER PRIMARY KEY, 
    DogName
);

INSERT INTO Dogs VALUES 
    ( NULL, 'Yelp' ),
    ( NULL, 'Woofer' );

SELECT last_insert_rowid(); 

Result:

2

As a side note, when I created the tables, I defined the first columns as autoincrement columns. This was defined implicitly when I used INTEGER PRIMARY KEY.

Therefore, when I select all rows, I can see that the last row contains the last_insert_rowid() value in that column.

SELECT * FROM Dogs;

Result:

DogId       DogName   
----------  ----------
1           Yelp      
2           Woofer    

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID.

The following query demonstrates this.

SELECT 
  rowid,
  * 
FROM Dogs;

Result:

DogId       DogId       DogName   
----------  ----------  ----------
1           1           Yelp      
2           2           Woofer    

Note that it is possible to create a column without using the inbuilt SQLite autoincrement feature. If you do this, then your “ID” column could have a different value to the ROWID value.

It is also possible to override the autoincrement value with your own explicit value. However, in this case, the ROWID value will still reflect this explicit value.

INSERT INTO Dogs 
VALUES ( 789, 'Fluff' );

SELECT 
  rowid,
  * 
FROM Dogs;

SELECT last_insert_rowid();

Result:

DogId       DogId       DogName   
----------  ----------  ----------
1           1           Yelp      
2           2           Woofer    
789         789         Fluff     

And of course, last_insert_rowid() will also reflect this latest ROWID value.

SELECT last_insert_rowid();

Result:

789

What is ROWID?

The ROWID is a 64-bit signed integer key that uniquely identifies the row within its table. All tables in SQLite have a ROWID unless the table is defined using WITHOUT ROWID.

The ROWID value can be accessed using one of the special case-independent names rowid, oid, or _rowid_ in place of a column name. If a table contains a user defined column using one of those names, then that name always refers the explicitly declared column and cannot be used to retrieve the integer ROWID value.

When you define a table with an autoincrement column, that column automatically uses the ROWID value for its row.

See How AUTOINCREMENT Works in SQLite for more information.