How to find a gap in range in SQL

Question

This question explains how to find the first "unused" number in a table, but how can I find the same so that I can define extra constraints. How do I alter the query so that I get the first unused number after that's greater than 100

e.g. If I have 23, 56, 100, 101, 103 in my table i should get 102.

Include a `where mo.id > 100` condition (in any of the queries in the accepted answer). — , Jul 22 '13 at 08:02
At the moment I'm using SQLite, but will most likely be migrating to PostgreSQL later on. — Kimvais, Jul 22 '13 at 10:20

Praveen Prasannan · Accepted Answer · 2013-07-22T12:02:39.253

in mysql and postgresql

SELECT  id + 1
FROM    test mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    test mi 
        WHERE   mi.id = mo.id + 1
        ) and mo.id> 100
ORDER BY
        id
LIMIT 1

fiddle for mysql and fiddle for postgresql

in ms sql

SELECT  TOP 1
        id + 1
FROM    test mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    test mi 
        WHERE   mi.id = mo.id + 1
        )
          and mo.id > 100
ORDER BY
        id

fiddle

score 4 · Answer 2 · answered Jul 22 '13 at 08:22

4

In Oracle Sql, you may try:

SELECT id
FROM
     (SELECT ID, lead(ID) OVER(ORDER BY ID) next_val FROM my_table t
     )
WHERE id +1 <> next_val
 AND id      >100;

answered Jul 22 '13 at 08:22

ajmalmhd04

2,582
6
23
41

That is in fact not Oracle specific. It uses standard ANSI SQL window function which would also work in Postgres, SQL Server 2012, DB2 and many other modern DBMS. – Jul 22 '13 at 09:04

agarici · Answer 3 · 2013-07-22T11:58:38.520

1

hope this will help you

SELECT MIN (id) + 1
  FROM myTable T1
 WHERE id >= 100
   AND NOT EXISTS (SELECT *
                     FROM myTable T2
                    WHERE T1.id + 1 = T2.id)

edited Jul 22 '13 at 11:58

answered Jul 22 '13 at 08:27

agarici

602
1
5
9

Works quite well, except if id 101 is free and 1000 the first taken, I will get 1001 for some weird reason.... – Kimvais Jul 22 '13 at 10:30
In this case, you must replace "id > 100" by "id >= 100" as shown in the query – agarici Jul 22 '13 at 11:58

score 0 · Answer 4 · answered Sep 11 '13 at 11:30

Using generate_series() for fun & profit:

CREATE table islands (num INTEGER NOT NULL PRIMARY KEY);

INSERT INTO islands (num ) VALUES
(23), (56), (100), (101), (103) ;

WITH total AS (
        SELECT generate_series(mima.bot, mima.top) AS num
        FROM ( SELECT MIN(num) AS bot , MAX(num) AS top FROM islands) mima
        )
SELECT num
FROM total tt
WHERE num >=100
AND NOT EXISTS (
        SELECT *
        FROM islands ii
        WHERE ii.num = tt.num
        )
        ;

score 0 · Answer 5 · answered Apr 23 '14 at 07:05

0

use this:

SELECT TOP 1 a1.id + 1 FROM test a1 left JOIN test a2 
ON a1.id = a2.id - 1 
WHERE a2.id IS NULL AND a1.id > 100

answered Apr 23 '14 at 07:05

aminj

1
1

How to find a gap in range in SQL

5 Answers5

Linked