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.
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.
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
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.