I have a table which includes a numeric "ID" column, values in the column
generally increment but there are some gaps.
I use the following query to get a list of all "missing" values
WITH RECURSIVE cte(x) AS (SELECT (SELECT Min(messages._id) FROM messages)
UNION ALL
SELECT cte.x + 1
FROM cte
WHERE cte.x < (SELECT Max(messages._id) FROM messages))
SELECT *
FROM cte
WHERE cte.x NOT IN (SELECT messages._id FROM messages)
Or to get the upper and lower bounds of the records that are present I can
use a couple of queries such as
SELECT messages._id
FROM messages
WHERE messages._id - 1 NOT IN (SELECT messages._id FROM messages)
which gives me (on my test values)
334
1077
1701
2385
2390
2393
and
SELECT messages._id
FROM messages
WHERE messages._id + 1 NOT IN (SELECT messages._id FROM messages)
334
1297
1701
2386
2390
3336
What I would like is a single query that summarises the values that are
present in (or missing from) a table. e.g.
334, 344
1077, 1297
1701, 1701
2385, 2386
2390, 2390
2393, 3336
Different approaches to this would be of interest
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
