How to find a "gap" in running counter with SQL?

Question

Hi,

I'd like to find first "gap" in a counter column in SQL table. For example, if there are values 1,2,4 and 5 I'd like to find out 3.

I can of course get the values in order and go through it manually but I'd like to know if there would be a way to do it in SQL.

In addition, it should be quite standard SQL, working with different DBMSs.

Answer 1

The first thing that came into my head. Not sure if it's a good idea to go this way at all, but should work. Suppose the table is t and the column is c:

SELECT t1.c+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL ORDER BY gap ASC LIMIT 1

Edit: This one may be a tick faster (and shorter!):

SELECT min(t1.c)+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL

Answer 2

In MySQL and PostgreSQL:

SELECT  id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id
LIMIT 1

In SQL Server:

SELECT  TOP 1
        id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id

In Oracle:

SELECT  *
FROM    (
        SELECT  id + 1 AS gap
        FROM    mytable mo
        WHERE   NOT EXISTS
                (
                SELECT  NULL
                FROM    mytable mi 
                WHERE   mi.id = mo.id + 1
                )
        ORDER BY
                id
        )
WHERE   rownum = 1

ANSI (works everywhere, least efficient):

SELECT  MIN(id) + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )

Answer 3

This works in SQL Server - can't test it in other systems but it seems standard...

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1))

You could also add a starting point to the where clause...

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1)) AND ID > 2000

So if you had 2000, 2001, 2002, and 2005 where 2003 and 2004 didn't exist, it would return 2003.

Answer 4

Inner join to a view or sequence that has a all possible values.

No table? Make a table. I always keep a dummy table around just for this.

create table artificial_range( 
  id int not null primary key auto_increment, 
  name varchar( 20 ) null ) ;

-- or whatever your database requires for an auto increment column

insert into artificial_range( name ) values ( null )
-- create one row.

insert into artificial_range( name ) select name from artificial_range;
-- you now have two rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have four rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have eight rows

--etc.

insert into artificial_range( name ) select name from artificial_range;
-- you now have 1024 rows, with ids 1-1024

Then,

 select a.id from artificial_range a
 where not exists ( select * from your_table b
 where b.counter = a.id) ;

Answer 5

My guess:

SELECT MIN(p1.field) + 1 as gap
FROM table1 AS p1  
INNER JOIN table1 as p3 ON (p1.field = p3.field + 2)
LEFT OUTER JOIN table1 AS p2 ON (p1.field = p2.field + 1)
WHERE p2.field is null;

Answer 6

There isn't really an extremely standard SQL way to do this, but with some form of limiting clause you can do

SELECT `table`.`num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
LIMIT 1

(MySQL, PostgreSQL)

or

SELECT TOP 1 `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL

(SQL Server)

or

SELECT `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
AND ROWNUM = 1

(Oracle)