SQLite | SQLite Functions | Returning the ROWID of the Last Inserted Row (last_insert_rowid Function)
The last_insert_rowid function returns the ROWID of the row most recently added to a table. This article explains how to use the function.
Using the last_insert_rowid Function
The last_insert_rowid function retrieves the ROWID of the most recently inserted row. Its syntax is as follows.
last_insert_rowid()
For details about ROWID, see Referencing ROWID and Its Relationship to INTEGER PRIMARY KEY. ROWID is a special value automatically assigned to data added to a table. You can use the last inserted row’s ROWID in a WHERE clause to retrieve that row.
–
Let’s try an example. First, create the following table.
create table user (id integer, name text);
sqlite> create table user (id integer, name text);
sqlite>
Add the following data with INSERT statements.
insert into user values (1, 'devkuma');
insert into user values (2, 'kimkc');
insert into user values (3, 'araikuma');
sqlite> insert into user values (1, 'devkuma');
sqlite> insert into user values (2, 'kimkc');
sqlite> insert into user values (3, 'araikuma');
sqlite>
Use last_insert_rowid in the WHERE clause of a SELECT statement to retrieve the row most recently added to the user table.
select * from user where rowid = last_insert_rowid();
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select * from user where rowid = last_insert_rowid();
id name
---------- ----------
3 araikuma
sqlite>
As shown above, the function can be used to retrieve the most recently inserted row.