SQL - Find Missing Int Values in Mostly Ordered Sequential Series

SQL - Find missing int values in mostly ordered sequential series

I've been there.

FOR ORACLE:

I found this extremely useful query on the net a while ago and noted down, however I don't remember the site now, you may search for "GAP ANALYSIS" on Google.

SELECT   CASE
             WHEN ids + 1 = lead_no - 1 THEN TO_CHAR (ids +1)
          ELSE TO_CHAR (ids + 1) || '-' || TO_CHAR (lead_no - 1)
         END
             Missing_track_no
   FROM   (SELECT   ids,
                    LEAD (ids, 1, NULL)
                     OVER (ORDER BY ids ASC)
                        lead_no
             FROM   YOURTABLE
             )
   WHERE   lead_no != ids + 1

Here, the result is:

MISSING _TRACK_NO
-----------------
       6

If there were multiple gaps,say 2,6,7,9 then it would be:

MISSING _TRACK_NO
-----------------
        2
       6-7
        9

How to check missing number sequence with wanted skips

Old school connect by version

with tn as(
    -- sample data
    Select 1 n from dual
    union all
    Select 4  from dual
    union all
    Select 26  from dual
    union all
    Select 30  from dual
    union all
    Select 52  from dual
)
select distinct n, delta, n+level nn
from (
    select n, delta
    from (
        select n, lead(n) Over(order by n) - n delta
        from tn) t
    where delta between 2 and 20
    ) t2
connect by level < delta 
order by n

Oracle SQL Find Sequential Values Based on Criteria in Another Column

Set operations is one way you could do this.

(edited to rewrite query as per comments - it now handles leading zeros in the varchar ID field)

---

WITH matches AS (
    SELECT to_number(ID) AS ID_integer
    FROM base_table
    WHERE LR = 'L'
    INTERSECT
    SELECT to_number(ID)-1
    FROM base_table
    WHERE LR = 'R'
)
SELECT bt.ID, bt.LR, m.ID_integer
FROM base_table bt, matches m
WHERE LR = 'L'
AND to_number(bt.ID) = m.ID_integer
UNION
SELECT bt.ID, bt.LR, m.ID_integer
FROM base_table bt, matches m
WHERE LR = 'R'
AND to_number(bt.ID)-1 = m.ID_integer
ORDER BY 3, 2;

How do I find a gap in running counter with SQL?

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
        )

Systems supporting sliding window functions:

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

How to find missing IDs in MySQL table

Using a Linux script populate a table test(id:auto-increment) up to your max value of your_table. Then execute something like

select id from test where id not in (select id from your_table)

The output will be the missing.

---

Related Topics