How to get the last row_id in a table without inserting anything

(1) By anonymous on 2021-04-11 16:43:14 [link] [source]

Hi, Im trying to get the last row_id from a table but without inserting anything in it. ( sqlite3_last_insert_rowid(db); this doesn't work if I do not insert any new row) I just need to know which is the max row id in the table so I can iterate from that point to row id 1. thanks a lot Fede

(2) By little-brother on 2021-04-11 16:59:18 in reply to 1 [link] [source]

select max(id) from t
-- or
select seq + 1 from sqlite_sequence where name = 't'

(3) By anonymous on 2021-04-11 18:51:09 in reply to 2 [link] [source]

Hi,I tried " select max(id) from table;" Table has 47 rows but the query give me an int of 1. this is my query.

query= "select MAX(id) from table1"; rc = sqlite3_exec(db,query.c_str(),NULL,0,NULL);

Do I miss something ? thanks

(4) By Keith Medcalf (kmedcalf) on 2021-04-11 19:54:39 in reply to 3 [link] [source]

That query assumes that id is an explicitly declared rowid.

Substitute the name of the rowid of your target table. For example, if your table declaration is thus:

create table dingdong
(
  whapydoodle integer primary key,
  otherstuffs
);

then you would select max(whapydoodle) from dingdong.

If you do not have an explicitly declared integer primary key (rowid) then you would use whatever alias for the rowid is available that has not been declared as a column in your table: rowid, _rowid_, or oid.

See the fine documentation: https://sqlite.org/c3ref/last_insert_rowid.html

(5.1) By Keith Medcalf (kmedcalf) on 2021-04-11 20:01:54 edited from 5.0 in reply to 4 [link] [source]

Note that:

select max(whapydoodle) from dingdong;

is equivalent to:

select max(rowid) from dingdong;

because there is no column named "rowid" so references to it refer to the rowid of the table, which happens to have the explicit name "whapydoodle".

(6) By Gunter Hick (gunter_hick) on 2021-04-12 05:01:44 in reply to 1 [link] [source]

Why do you think you need to know the max(rowid)? Are you implying that numerical order of rowids == chronological order of insertion? It would take AUTOINCREMENT to guarantee that SQLite does not re-use the rowids of deleted records; and even that does not guarantee that the rowids assigned are contigous. Some may be left out in special cases where a rowid is already assigned but the insert fails due to constraints.

If you need to retrieve rows in a particular order, you must provide an ORDER BY clause; not providing an ORDER BY clause leaves SQLite the freedom to provide rows in any order it chooses (i.e. visitation oder), which may change if the query plan changes (typically after running analyse when the "shape" of your data has changed or upgrading SQLite to a newer/better query planner).

To iterate of all rows in descending order of rowid, use

SELECT ... FROM table ORDER BY ROWID DESC;

there is no need to manually iterate rowids.

(7) By anonymous on 2021-04-12 09:49:06 in reply to 6 [link] [source]

Thanks a lot, that is exactly what I intend to do but not knowing it I tried the numerical order.

(8) By Richard Hipp (drh) on 2021-04-12 11:34:28 in reply to 3 [source]

sqlite3_exec() does not return the answer of your query. sqlite3_exec() returns an integer code that indicates whether or not the query succeeded. If you want the answer, you have to set a callback function and retrieve the answer in the callback function.