Excellent! You were on the mark; I am trying to fill the holes as records are deleted.
Thanks Igor! On Thu, 2009-10-08 at 19:45 -0400, Igor Tandetnik wrote: > 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 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
