Get First Available Index In The Table, Including 0

I have a table called player_chest, which has the following fields: idx integer NOT NULL, player_id integer NOT NULL, chest_id integer NOT NULL, I am using the following query to

Solution 1:

One solution uses window functions:

select (casewhen min_idx >0then0else1+min(idx) filter (where next_idx isdistinctfrom idx +1)
        end)
from (select pc.*,
             lead(idx) over (partitionby player_id orderby idx) as next_idx,
             min(idx) over (partitionby player_id) as min_idx
      from player_chest pc
      where player_id =2
     ) pc
groupby player_id, min_idx;

If you want a version that returns 0 even if the player is not in the table, then:

select (casewhenmax(min_idx) >0ormax(min_idx) isnullthen0else1+min(idx) filter (where next_idx isdistinctfrom idx +1)
        end)
from (select pc.*,
             lead(idx) over (partitionby player_id orderby idx) as next_idx,
             min(idx) over (partitionby player_id) as min_idx
      from player_chest pc
      where player_id =2
     ) pc