How do I find a gap in running counter in Informix table

Question

How to find the first "gap" in auto increment column in an Informix`table.

For example:

My table mission like that:

emp_num  serial_request  year_request
33         45            2012
667        46            2012
588        50            2012
78         53            2012
89         33            2013

I want the first gap in 2012 ----> 47

I have searched and found the following question:

How do I find a “gap” in running counter with SQL? but the answer doesn't cover Informix.

score 1 · Accepted Answer · edited May 23 '17 at 12:14

The selected answers for the other question only need trivial modifications to work with Informix.

For instance — one solution given in the other question is:

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

The Informix equivalent, adapted to your schema, is:

SELECT LIMIT 1 serial_request + 1 AS id
  FROM mission AS m1
 WHERE NOT EXISTS
       (SELECT *
          FROM mission AS m2 
         WHERE m2.serial_request = m1.serial_request + 1
       )
 ORDER BY id

Given the sample data, it correctly produces the answer 34 (the first number missing after a number that appears in the table). To get the first number missing after a row entered in 2012, you simply need to add a condition:

   AND m1.year_request = 2012

Leading to:

SELECT LIMIT 1 serial_request + 1 AS id
  FROM mission AS m1
 WHERE NOT EXISTS
       (SELECT *
          FROM mission AS m2 
         WHERE m2.serial_request = m1.serial_request + 1
       )
   AND m1.year_request = 2012
 ORDER BY id

That does yield the answer 47 as requested.

You could also use the OLAP windowing functions solution (again, based on SQL from the other question):

SELECT LIMIT 1
       q.previd + 1
  FROM (SELECT serial_request,
               LAG(serial_request) OVER (ORDER BY serial_request) previd
          FROM mission
         WHERE year_request = 2012
       ) AS q
 WHERE q.previd <> q.serial_request - 1
 ORDER BY q.previd;

This yields 47 too. We can debate about the relative merits of the plus and minus 1 in the code and where they should go.

Test Schema and Data

DROP TABLE IF EXISTS mission;

CREATE TEMP TABLE mission
(
    emp_num         INTEGER NOT NULL PRIMARY KEY,
    serial_request  SERIAL NOT NULL UNIQUE,
    year_request    SMALLINT NOT NULL
);

INSERT INTO mission(emp_num, serial_request, year_request) VALUES(33, 45, 2012);
INSERT INTO mission(emp_num, serial_request, year_request) VALUES(667, 46, 2012);
INSERT INTO mission(emp_num, serial_request, year_request) VALUES(588, 50, 2012);
INSERT INTO mission(emp_num, serial_request, year_request) VALUES(78, 53, 2012);
INSERT INTO mission(emp_num, serial_request, year_request) VALUES(89, 33, 2013);

Testing: Informix 12.10.FC5 running on Mac OS X 10.11.6.

You are just amazing :O – Anyname Donotcare Aug 07 '16 at 18:08 — Anyname Donotcare, Aug 07 '16 at 18:08

How do I find a gap in running counter in Informix table

1 Answers1

Test Schema and Data