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
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
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 ro
On 4/15/2016 6:04 AM, Paul Sanderson wrote:
> but I would like a table of the rows that bracket the missing rows e.g.
>
> 3, 5
> 5, 8
> 11, 14
with Brackets as (
select t1.id low, (select min(t2.id) from mytable t2 where t2.id >
t1.id) high
from mytable t1
)
select low, high from Brackets w
4 matches
Mail list logo