CODEWITHSUNDEEP
sqlite
ThumbGen
ThumbGen

Reputation: 182

How to query gaps and list lower/upper bounds of the gap in SQLite?

I am struggling with listing the gaps including their lower and upper bounds in a simple SQLite table.

The table:

id

1

2

5

6

8

What I want is a result like (cnt is counting the missing integers):

lb ub cnt

2 5 2

6 8 1

I have already found the gaps, but no idea how to put lower and upper bounds together and count the missing integers:

SELECT id FROM table mo WHERE NOT EXISTS ( SELECT NULL FROM messages mi WHERE mi._id = mo._id + 1 ) ORDER BY id

Replacing the condition with -1 I get the other bounds.

Upvotes: 1

Views: 157

Answers (2)

Shawn
Shawn

Reputation: 52549

Looks like a straightforward gaps and islands problem.

CREATE TABLE test(id INTEGER PRIMARY KEY);
INSERT INTO test VALUES (1), (2), (5), (6), (8);
WITH ranges AS (SELECT id AS lb, lead(id, 1) OVER (ORDER BY id) AS ub FROM test) 
SELECT lb, ub, ub - lb - 1 AS cnt FROM ranges WHERE ub - lb > 1;

gives

lb          ub          cnt       
----------  ----------  ----------
2           5           2         
6           8           1

(Note: The above requires Sqlite 3.25 or newer)

Upvotes: 1

forpas
forpas

Reputation: 164164

You must get the gaps with a subequery:

select t.id lb, t.next ub, (t.next - t.id - 1) cnt from (
  select 
    id, 
    (select min(id) from tablename where t.id < id) next
  from tablename t
  ) t
where t.next is not null and t.next - t.id > 1

See the demo

Upvotes: 2

Related Questions