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
