Paul Sanderson wrote:
> What I would like is a single query that summarises the values that are
> present in (or missing from) a table.
A row is the start of a range if there is no previous row:
WITH ranges(first) AS (
SELECT _id
FROM messages
WHERE _id - 1 NOT IN (SELECT _id FROM messages)
)
The corresponding last row is the first row at or behind the start row
that has no next row:
SELECT first,
(SELECT min(_id)
FROM messages
WHERE _id >= ranges.first
AND _id + 1 NOT IN (SELECT _id FROM messages)
) AS last
FROM ranges;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
