On Aug 30, 2010, at 10:07 PM, Eric Smith wrote: > 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.
Thanks for the suggestion. My data sets are not too large, so a full table scan is probably not a big deal. Nonetheless, it's good to be prepared in case they grow ;) Scott > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
