Ahhh...you didn't let autoincrement do it's job...
sqlite> create table Auto(id integer primary key autoincrement, data text); sqlite> insert into Auto values (NULL, 'one'); sqlite> insert into Auto values (NULL, 'two'); sqlite> insert into Auto values (NULL, 'three'); sqlite> select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three sqlite> delete from Auto where data='two'; sqlite> insert into Auto values(NULL, 'most recent'); sqlite> select id, data from Auto where id=(select max(id) from Auto); 4|most recent I wouldn't trust rowid given the description that it can reuse numbers. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 12:44 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 1:33 PM, Black, Michael (IS) wrote: > That's because my id is autoincrement and yours is not. What do you mean, mine vs yours? I continue with your example, using the same setup. To avoid any confusion, here's a full session: sqlite> create table NoAuto(id integer primary key, data text); sqlite> insert into NoAuto values (NULL, 'one'); sqlite> insert into NoAuto values (NULL, 'two'); sqlite> insert into NoAuto values (NULL, 'three'); sqlite> select rowid, data from NoAuto where rowid=(select max(rowid) from NoAuto); 3|three sqlite> delete from NoAuto where data='two'; sqlite> insert into NoAuto values(2, 'most recent'); sqlite> select rowid, data from NoAuto where rowid=(select max(rowid) from NoAuto); 3|three sqlite> create table Auto(id integer primary key autoincrement, data text); sqlite> insert into Auto values (NULL, 'one'); sqlite> insert into Auto values (NULL, 'two'); sqlite> insert into Auto values (NULL, 'three'); sqlite> select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three sqlite> delete from Auto where data='two'; sqlite> insert into Auto values(2, 'most recent'); sqlite> select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three Note how the record with data=='most recent' was never selected, despite being inserted by the most recent successful INSERT statement. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
