That simple - I was over thinking it cheers
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 On 18 July 2017 at 11:19, Clemens Ladisch <clem...@ladisch.de> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
