last_insert_row_id isn't guaranteed either...and here's the complete example of using autoincrement that is guaranteed to work and not be volatile.
D:\SQLite>sqlite3 t1.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" 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> .quit D:\SQLite>sqlite3 t1.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select last_insert_rowid() from t1 limit 1; 0 last_insert_rowid is volatile. sqlite> select id,data from t1 order by id desc limit 1; 3,three sqlite> delete from t1 where data='two'; sqlite> insert into t1 values(null,'two'); sqlite> select id,data from t1 order by id desc limit 1; 4|two sqlite>.quit D:\SQLite>sqlite3 t1.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select id,data from t1 order by id desc limit 1; 4|two 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 3:01 PM To: [email protected] Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 3:43 PM, Black, Michael (IS) wrote: > I thought we were answering the question "how can I retrive the last row" -- > though we never got a definition of what "last" meant. I assumed "last > inserted". Defining the meaning of the word "last" in terms of an expression that includes the word "last" is a bit circular. Let's say, "most recently inserted". > Sure you can construct an example that doesn't work. But he didn't ask how > NOT to do it. OK then, show how to do it. You haven't, yet. > Don't you agree that using autoincrement properly guarantees > retrieving the last inserted row? Or are you maintaining that is a false > statement? I maintain that the request you have shown - select id, data from Auto where id=(select max(id) from Auto); - doesn't always retrieve the most recently inserted row, and thus doesn't in fact solve the problem you claim it solves. > I'd like to see an example to disprove it if you maintain that its false. I have shown one. -- 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
