Sure it does. sqlite> select rowid,id,* from t1 where id=(select max(id) from t1); 4|4|4|two
"two" was the last succesful insert in my example. I'm now noticing though that rowid is not working as documented. http://www.sqlite.org/autoinc.html Says "If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert." But....if I delete the max rowid I expect it to be re-used based on the above.... sqlite> create table t1(id integer primary key autoincrement,data text); sqlite> insert into t1 values(null,'one'); sqlite> insert into t1 values(null,'two'); sqlite> insert into t1 values(null,'three'); sqlite> select rowid,* from t1; 1|1|one 2|2|two 3|3|three sqlite> delete from t1 where data='two'; sqlite> insert into t1 values(null,'two'); sqlite> select rowid,* from t1; 1|1|one 3|3|three 4|4|two sqlite> delete from t1 where data='two'; sqlite> insert into t1 values(null,'two'); sqlite> select rowid,* from t1; 1|1|one 3|3|three 5|5|two << This should be 4,5 and not 5,5 according to the docs as "3" was the largest in the table prior to insert. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: [email protected] [[email protected]] on behalf of Igor Tandetnik [[email protected]] Sent: Monday, August 22, 2011 11:49 AM To: [email protected] Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 12:42 PM, Black, Michael (IS) wrote: > autoincrement does keep order regardless of deletes. rowid won't guarantee > it. > > > > So you don't "have" to add your own unless you need more than autoincrement. > > > > sqlite> create table t1(id integer primary key,data text); > sqlite> insert into t1 values(NULL,'one'); > sqlite> insert into t1 values(NULL,'two'); > sqlite> insert into t1 values(NULL,'three'); > sqlite> select rowid from t1 where rowid=(select max(rowid) from t1); > 3 > sqlite> delete from t1 where data='two'; > sqlite> insert into t1 values(NULL,'two'); > sqlite> select rowid from t1 where rowid=(select max(rowid) from t1); > 4 > > > > You'll always get the "last" record that was successfully inserted. sqlite> insert into t1 values(2, 'another two'); sqlite> select rowid from t1 where rowid=(select max(rowid) from t1); 4 It seems that the record your statement returns is not the record that was successfully inserted most recently. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
