Thanks 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 15 April 2016 at 11:14, Rowan Worth <rowanw at dugeo.com> wrote: > 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 >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users