How to read the last record in SQLite table?

Question

Is there a way to read the value of the last record inserted in an SQLite table without going through the previous records ?

I ask this question for performance reasons.

based on last insertion that happened. The order is the ID which is auto incremented. — , Jun 30 '14 at 16:14
So you're saying you're looking for how to retrieve the record with the maximum ID? — O. R. Mapper, Jun 30 '14 at 16:17
Does this answer your question? [How to get Last record from Sqlite?](https://stackoverflow.com/questions/9902394/how-to-get-last-record-from-sqlite) — WinEunuuchs2Unix, Feb 27 '21 at 14:22

ravenspoint · Accepted Answer · 2014-06-30T16:56:42.177

6

There is a function named sqlite3_last_insert_rowid() which will return the integer key for the most recent insert operation. http://www.sqlite.org/c3ref/last_insert_rowid.html

This only helps if you know the last insert happened on the table you care about.

If you need the last row on a table, regardless of wehter the last insert was on this table or not, you will have to use a SQL query

SELECT * FROM mytable WHERE ROWID IN ( SELECT max( ROWID ) FROM mytable );

edited Jun 30 '14 at 16:56

answered Jun 30 '14 at 16:49

ravenspoint

19,093
6
57
103

You can also do `SELECT * FROM mytable ORDER BY ROWID DESC LIMIT 1`, though I don't know which would be faster. – Gohn67 Feb 21 '20 at 16:40

score 1 · Answer 2 · answered Jun 30 '14 at 16:31

When you sort the records by ID, in reverse order, the last record will be returned first. (Because of the implicit index on the autoincrementing column, this is efficient.)

If you aren't interested in any other records, use LIMIT:

SELECT *
FROM MyTable
ORDER BY _id DESC
LIMIT 1

How to read the last record in SQLite table?

2 Answers2