¿Cómo encuentro un "espacio" en el contador de ejecución con SQL?

gaps-and-islands

Question

Me gustaría encontrar el primer "espacio" en una columna de contador en una tabla de SQL. Por ejemplo, si hay valores 1,2,4 y 5 me gustaría saber 3.

Por supuesto, puedo ordenar los valores y revisarlos manualmente, pero me gustaría saber si habría una forma de hacerlo en SQL.

Además, debería ser bastante estándar SQL, trabajando con diferentes DBMSes.

Answer 1

Aquí hay una solución SQL estándar que se ejecuta en todos los servidores de bases de datos sin cambios:

select min(counter + 1) FIRST_GAP
    from my_table a
    where not exists (select ''x'' from my_table b where b.counter = a.counter + 1)
        and a.counter <> (select max(c.counter) from my_table c);

Ver en acción para;

• PL / SQL a través de livesql de Oracle ,
• MySQL a través de sqlfiddle ,
• PostgreSQL a través de sqlfiddle
• MS Sql a través de sqlfiddle

Answer 2

En MySQL y 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

En 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

En 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 (funciona en todas partes, menos eficiente):

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

Sistemas compatibles con funciones de ventana deslizante:

SELECT  -- TOP 1
        -- Uncomment above for SQL Server 2012+
        previd
FROM    (
        SELECT  id,
                LAG(id) OVER (ORDER BY id) previd
        FROM    mytable
        ) q
WHERE   previd <> id - 1
ORDER BY
        id
-- LIMIT 1
-- Uncomment above for PostgreSQL

Answer 3

Esto explica todo lo mencionado hasta ahora. Incluye 0 como punto de partida, que será predeterminado si no existen valores también. También agregué las ubicaciones apropiadas para las otras partes de una clave multivalor. Esto solo ha sido probado en SQL Server.

select
    MIN(ID)
from (
    select
        0 ID
    union all
    select
        [YourIdColumn]+1
    from
        [YourTable]
    where
        --Filter the rest of your key--
    ) foo
left join
    [YourTable]
    on [YourIdColumn]=ID
    and --Filter the rest of your key--
where
    [YourIdColumn] is null

Answer 4

Esto funciona en SQL Server, no puede probarlo en otros sistemas, pero parece estándar ...

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

También podría agregar un punto de partida a la cláusula where ...

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

Entonces, si tuviera 2000, 2001, 2002 y 2005 donde 2003 y 2004 no existían, regresaría en 2003.

Answer 5

Funciona también para tablas vacías o con valores negativos. Acabo de probar en SQL Server 2012

 select min(n) from (
select  case when lead(i,1,0) over(order by i)>i+1 then i+1 else null end n from MyTable) w

Answer 6

Lo primero que se me vino a la cabeza. No estoy seguro si es una buena idea ir por este camino en absoluto, pero debería funcionar. Supongamos que la tabla es t y la columna es 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

Editar: Esta puede ser una marca más rápida (¡y más corta!):

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 7

Mi conjetura:

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 8

Para PostgreSQL

Un ejemplo que hace uso de consulta recursiva.

Esto podría ser útil si desea encontrar un espacio en un rango específico (funcionará incluso si la tabla está vacía, mientras que los otros ejemplos no)

WITH    
    RECURSIVE a(id) AS (VALUES (1) UNION ALL SELECT id + 1 FROM a WHERE id < 100), -- range 1..100  
    b AS (SELECT id FROM my_table) -- your table ID list    
SELECT a.id -- find numbers from the range that do not exist in main table
FROM a
LEFT JOIN b ON b.id = a.id
WHERE b.id IS NULL
-- LIMIT 1 -- uncomment if only the first value is needed

Answer 9

Realmente no hay una forma de SQL extremadamente estándar para hacer esto, pero con alguna forma de cláusula de limitación puedes hacer

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)

o

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

(Servidor SQL)

o

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

(Oráculo)

Answer 10

Si usa Firebird 3, esto es lo más elegante y simple:

select RowID
  from (
    select `ID_Column`, Row_Number() over(order by `ID_Column`) as RowID
      from `Your_Table`
        order by `ID_Column`)
    where `ID_Column` <> RowID
    rows 1

Answer 11

Sus respuestas funcionan bien si tiene un primer valor id = 1, de lo contrario, este espacio no será detectado. Por ejemplo, si los valores de su ID de tabla son 3,4,5, sus consultas devolverán 6.

Hice algo como esto

SELECT MIN(ID+1) FROM (
    SELECT 0 AS ID UNION ALL 
    SELECT  
        MIN(ID + 1)
    FROM    
        TableX) AS T1
WHERE
    ID+1 NOT IN (SELECT ID FROM TableX) 

Answer 12

Unión interna a una vista o secuencia que tiene todos los valores posibles.

¿Incapaz? Hacer una mesa. Siempre guardo una mesa ficticia solo para esto.

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

Entonces,

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

Answer 13

select min([ColumnName]) from [TableName]
where [ColumnName]-1 not in (select [ColumnName] from [TableName])
and [ColumnName] <> (select min([ColumnName]) from [TableName])