SQLite Find ROWID of most recent or next ROWID

Question

So I have a table with data about an image. The table looks something like this...

ROWID|title|description|file_path

The file path contains the name of the image. I want to rename the image to match the ROWID.

How do I get the latest ROWID? I need to also account for rows that have been deleted as I am using this as an autoincremented primary key. Because, if a row within the table has been deleted it is possible for the table to look like this...

1|title A|description A|..\fileA.jpg
2|title B|description B|..\fileB.jpg
5|title E|description E|..\fileE.jpg
7|title G|description G|..\fileG.jpg

On top of that there could be one or more rows that have been deleted so the next ROWID could be 10 for all I know.

I also need to account for an fresh new table or a table that has had all data deleted and the next ROWID could be 1000.

In summary, I guess the real question is; Is there a way to find out what the next ROWID will be?

possible duplicate of [Effective way to get unused rowid in Android/SQLite?](http://stackoverflow.com/questions/15028621/effective-way-to-get-unused-rowid-in-android-sqlite) — CL., Oct 28 '13 at 17:58

score 3 · Answer 1 · answered Oct 28 '13 at 17:57

3

If you have specified AUTOINCREMENT in primary key field and table is not empty this query will return latest ROWID for table MY_TABLE:

SELECT seq
FROM sqlite_sequence 
WHERE name = 'MY_TABLE'

answered Oct 28 '13 at 17:57

SqliteDog

86
3

score 0 · Answer 2 · edited Oct 29 '13 at 09:22

0

What language? Looks like the c API has the following function:

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

http://www.sqlite.org/c3ref/last_insert_rowid.html

You could also just do:

select MAX(rowid) from [tablename];

edited Oct 29 '13 at 09:22

LS_ᴅᴇᴠ

10,823
1
23
46

answered Oct 28 '13 at 17:44

jeremy

4,294
3
22
36

score 0 · Answer 3 · answered Oct 30 '13 at 04:10

Unfortunately neither of these methods completely worked the way I needed them to, but what i did end up doing was....

insert data into table with the fields I needed the rowid for filled with 'aaa' then updated the rows with the data.

This seemed to solve my current issue. Hopefully it doesn't cause another issue down the road.

score 0 · Answer 4 · answered Feb 15 '14 at 12:55

I think last_insert_rowid is what you want, usually.

Note that the rowid behavior is different depending on the autoincrement flag - either it will monotonically increase, or it will assume any free id. This will not usually affect any smaller use cases though.

SQLite Find ROWID of most recent or next ROWID

4 Answers4