SQLiteDatabase.LastRowID

I get the logic of the LastRowID call and see its usefulness but…

Is there an easy way to get the LastRowID from a table when that table may not have been the one having the most recent row added? Something like

Dim lastly as integer lastly = theDB.LastRowID (some_other_table)
Right now, I’m doing a SelectSQL on the Max(rowID) but it seems that there should be a better (cleaner) way.

Check out the following link, Dale … I believe that IDENT_CURRENT( ‘table_or_view’ ) is what you want.

link text

I believe that IDENT_CURRENT() is not a SQL standard and while available in MS SQL Server, I don’t believe an equivalent is available in SQLite which the OP’s thread subject line suggests is the target database. AFAIK, using MAX(rowID) is the cleanest solution in SQLite when needing the value at an arbitrary time as opposed to saving it immediately after performing an INSERT.

For SQLite have a look at the sqlite_sequence table.

wouldn’t this do what you want?

SELECT max(RowID) as LastRowID FROM myTable

All SQLite tables have RowID regardless if you defined it or not.

It would work if all the tables, or the ones in question, had a key field specified as AUTOINCREMENT. No guarantee of that.

[quote=470011:@Dave S]wouldn’t this do what you want?

SELECT max(RowID) as LastRowID FROM myTable

All SQLite tables have RowID regardless if you defined it or not.[/quote]
Might work, thanks. I’ll have to plug it into the app and try it

so … you have no guarantee also that the lastrowid is the max(rowid) ?
because you could insert a rowid that is not the maximum one ?

It is very bad practice to define a column/field with the name of ROWID, as this has special meaning to SQLite (if not others)

If you define an autoincrement PK for example… its value is stored in ROWID , and an alias is created to your actual named field. If you do not create a PK… the ROWID exists anyways

https://sqlite.org/autoinc.html

Not at all, it’s the usual default name of guess what? The RowId. People like to call it by the short name of just “Id” too. The difference of an explicit defined column named rowId (any column of “INTEGER PRIMARY KEY” or “INTEGER PRIMARY KEY AUTOINCREMENT” type) and the implicit one, is that the implicit one is just the ordinal number of the physical record, a virtual column (as its aliases ROWID and OID, that can be used too as column names or used to access the virtual rowid value if an explicit one is not present). You can’t trust in the value of the virtual RowId value in a table without a PK because it can cause a mess if used by a table that had deleted rows, specially after a vacuum, as their “ids” (values) change, but an explicit RowID doesn’t, because it is a real integer column holding a real recorded value. If you have a column called ID as INTEGER PRIMARY KEY AUTOINCREMENT, the value of RowID (or RowID or OID) will be the value of this column. If you have a column called RowId as INTEGER PRIMARY KEY AUTOINCREMENT, the value of RowID is obviously its value (but also RowID or OID). The above applies to SQLITE only, behaviors can change in different DB Engines / SQL dialects.