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. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: [email protected] [[email protected]] on behalf of Simon Slavin [[email protected]] Sent: Monday, August 22, 2011 11:32 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: Last record in db On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote: > On 8/22/2011 9:52 AM, Black, Michael (IS) wrote: >> No...if you use autoincrement you can guarantee that "last" will be the last >> record inserted. > > There's no contradiction. "Last" is still defined only for ordered sets > - you just chose a particular ordering, by rowid. And even if you do that, it's easy to break the expected ordering: > .schema t1 CREATE TABLE t1(id INT,data TEXT); > select * from t1 ; id data ---------- ---------- 1 one 2 two 3 tre > delete from t1 where data = 'two'; > insert into t1 (it, data) values (2, 'second'); Now the 'last' record is not the one with the highest value in the id column. Igor is right. The question from the original poster doesn't mean anything in SQL. SQL has no concept of an order for rows, so it has no idea which row is 'first' or 'last'. If you, the programmer have your own idea what is first or last, write your own numbers into the database. Simon. _______________________________________________ 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
