Re: [sqlite] Summarising (missing) values

[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 demo licence

On 18 July 2017 at 11:19, Clemens Ladisch  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

Re: [sqlite] Summarising (missing) values

[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 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] Summarising (missing) values

[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
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