Bob Lauria <[email protected]> wrote:
> I have a table keyed by an integer column. Each time I insert a row
> into the table I would like to use the lowest value that does not
> currently exist in the table as a key. Is there a query that will
> provide me with the lowest numeric value (for a column) that does not
> exist in the table?
I assume you want a positive value that matches these conditions. Try this:
select case when 1 not in (select id from mytable) then 1 else (
select min(id) + 1 from mytable where id + 1 not in (select id from mytable)
) end;
This is for the case where you are also deleting recors, so the sequence of ids
may have holes that you want to fill in. If you
never delete, then it's simply
select coalesce(max(id) + 1, 1) from mytable;
or, better still, declare the column as INTEGER PRIMARY KEY and let SQLite
increment it automatically.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
