SET, > >> In a table I have records with id's 2,4,5,8. How can I receive a list > >> with values 1,3,6,7. I have tried in this way > >> > >> SELECT t1.id + 1 > >> FROM table t1 > >> WHERE NOT EXISTS ( > >> SELECT * > >> FROM table t2 > >> WHERE t2.id = t1.id + 1 > >> ) > >> > >> but it's not working correctly. It doesn't bring all available positions. > > > >The SQL looks OK and should work. > > > >What is the datatype of the ID column of the table? > > No Sean, that would return 3 and 6, 1 and 7 wouldn't be returned by such a > statement (1 because that is less than the lowest value in table and 7 > because 6 isn't in the table)
Once again, your keen I found an error! > The best way to do something like this, is (in my > opinion) to use EXECUTE BLOCK (it is possible to do similar things using a > recursive CTE, but that's more complex, slower and quickly fails due to too > many recursions): > > execute block returns (id integer) as > declare variable idmax integer; > begin > id = 1; > select max(id) from table into :idmax; > while (id < idmax) do > begin > if (not exists(select * from table where id = :id)) then suspend; > id = id + 1; > end > end If we are going the EXECUTE BLOCK approach, which I agree would be better. Then, the optimal logic would be: execute block returns (id integer) as declare variable Next_ID integer; begin ID = 1; FOR SELECT ID FROM Table ORDER BY ID INTO :Next_ID DO Next_ID = Next_ID - 1; WHILE (Next_ID >= ID) DO BEGIN SUSPEND; ID = ID +1; END ID = ID +1; end The above has the benefit of not requiring table reads to determine missing items. Sean