I have a table with few IDs say 100000, 99999, 99998, 1000, 10, 5. Now my requirement is to get max ID which is not used.
Sample data:
CREATE TABLE foo
AS
SELECT *
FROM ( VALUES (10000),(9999),(9998),(100),(5),(2) )
AS t(id);
In above case, it should be 99997. Any query to get this max available ID?
I've used lead() function to get difference between current id and next id on descending order.
with ct as
(
select id, lead(id) over (order by id desc) as nextid
from foo
)
select id -1 as next_id
from ct
where id - nextid > 1
order by id desc
limit 1;
next_id
-------
9997
drop table foo;
Rextester here
One method uses left join:
select t.id - 1
from t left join
t tprev
on t.id = tprev.id + 1
where tprev.id is null
order by t.id desc
limit 1;
Another uses lag():
select t.id - 1
from (select t.*, lag(id) over (order by id) as prev_id
from t
) t
where prev_id <> id - 1
order by id desc
limit 1;