sqlite> create table a(a integer primary key);
sqlite> SELECT a1.a, a2.a FROM a AS a1, a AS a2
WHERE a2.a > a1.a
GROUP BY a1.a
HAVING a2.a = min(a2.a)
AND a2.a <> a1.a + 1;
3|5
5|8
11|14
IIRC "HAVING x = min(x)" is not portable SQL but it seems to work in sqlite.
-Rowan
On 15 April 2016 at 18:04, Paul Sanderson <sandersonforensics at gmail.com>
wrote:
> I confused myself with that title.
>
> I have a DB with 300 odd rows with an integer primary key, there are a
> few breaks in the key numbering such that I have something like
>
> 1
> 2
> 3
> 5
> 8
> 9
> 10
> 11
> 14
>
> I can generate a list of misisng rows easily enough , eg
>
> 4
> 6
> 7
> 12
> 13
>
> but I would like a table of the rows that bracket the missing rows e.g.
>
> 3, 5
> 5, 8
> 11, 14
>
> any ideas how I might go about this?
>
> 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
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
