0

I have the following integer values in a column [1,2,3,4,6,7,9,11,12], I want to write a query that gives me the minimum positive missing integer that should be 5 in this case

thank you

Mohammed Housseyn Taleb
  • 1,748
  • 28
  • 64
  • 2
    [How do I find a “gap” in running counter with SQL?](http://stackoverflow.com/questions/1312101/how-do-i-find-a-gap-in-running-counter-with-sql) – Lukasz Szozda Jul 16 '16 at 09:23

1 Answers1

1

Here is one method: 

select min(t.col) + 1
from t
where not exists (select 1 from t t2 where t2.col = t.col + 1);

EDIT:

If I assume that you want "1" if it is missing from the sequence, then:

select (case when max(tm.mincol) > 1 then 1
             else min(t.col) + 1
        end) as first_missing
from t cross join
     (select min(col) as mincol, max(col) as maxcol
      from t
     ) tm
where not exists (select 1 from t t2 where t2.col = t.col + 1);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786