android - Search missing rowId in sqlite -


in table there rows have been deleted. how can find first missing id?

ex:

id    data 1    row1 2    row2 4    row3 8    row4 

how can find id 3 missing? needs run on android 3.0 minimum

in sqlite 3.8.3 or later, can use following query find first unused id in specific range:

with recursive all_ids(id) (   values(1)   union   select id + 1   all_ids   limit 99999 ) select id all_ids not exists (select 1                   mytable                   rowid = all_ids.id) limit 1 

in earlier sqlite versions, cannot dynamically generate values. however, desired id 1 larger existing id, can take ids table , check whether next 1 exists (the 0 special case handle empty table):

select id + 1 (select 0 id       union       select rowid       mytable       rowid <= 99999      ) ids not exists (select 1                   mytable                   rowid = ids.id + 1) limit 1 

Comments

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

Kivy: Swiping (Carousel & ScreenManager) -

jdbc - Not able to establish database connection in eclipse -