Scott Frankel wrote: > Hi all, > > How does one find the next value of a serial item? Given a simple > table with a serial primary key, I'd like to get the next available > integer key value. eg: > > CREATE TABLE foo ( > foo_id SERIAL PRIMARY KEY, > name text DEFAULT NULL); > > SELECT foo_id, name FROM foo; > > > 1|red > 2|green > 3|blue > > SELECT ???; > > > 4
You probably don't want 'SELECT max(foo_id)+1' because I think that does a full table scan. I recommend you re-define your key as 'foo_id INTEGER PRIMARY KEY'. If you want to INSERT a new value with the next id you can just say INSERT INTO foo(foo_id,name) VALUES(NULL, 'name') and get the newly-inserted id by saying SELECT last_insert_rowid(). If you only want to get what *would* be the next value, I believe SELECT 1 + (SELECT foo_id FROM foo ORDER BY foo_id DESC LIMIT 1) will run in constant time. Eric -- Eric A. Smith Some people have told me they don't think a fat penguin really embodies the grace of Linux, which just tells me they have never seen an angry penguin charging at them in excess of 100mph. They'd be a lot more careful about what they say if they had. -- Linus Torvalds, 1996 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users