Re: [sqlite] Summarising (missing) values

2017-07-18 Thread Paul Sanderson
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

Re: [sqlite] Summarising (missing) values

2017-07-18 Thread Clemens Ladisch
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

[sqlite] Summarising (missing) values

2017-07-18 Thread Paul Sanderson
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