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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users