find gaps in numbers

[Antonio Policelli]

hello, i have a table

create table tt (
col int
)

that has values with gaps. for example select col from tt order by
col

1
2
3
7
8
12
14
25
26

I need to run s select statement to find where the gaps span >= x
numbers, so if x = 10, I would get the answer
14 11
where 14 is where is starts and 11 is the spaces? i am not too clever
so this is hard for me

help p[lease?

AP

[Itzik Ben-Gan]

Here goes...

DECLARE @x AS INT
SET @x = 10

SELECT T1.col, MIN(T2.col)-T1.col AS diff
FROM tt AS T1 JOIN tt AS T2
ON T2.col > T1.col
GROUP BY T1.col
HAVING MIN(T2.col)-T1.col >= @x

--
BG, SQL Server MVP
Solid Quality Learning

"Antonio Policelli" <bla...@yahoo.com> wrote in message
news:624f68b2.02122...@posting.google.com...

[Anith Sen]

Here is a simple way in T-SQL:

SELECT TOP 1 col,
(SELECT TOP 1 t1.col - tt.col
FROM tt t1
WHERE t1.col > tt.col
ORDER BY col)
FROM tt
WHERE col >= 13

You can avoid TOP using MAX/MIN or use derived tables as well.

--
- Anith
(Please respond only to newsgroups)

[Anith Sen]

Seeing the other response, I think I misread the post. Here is
another one:

SELECT col, diff
FROM (
SELECT col,(SELECT TOP 1 t1.col - tt.col

FROM tt t1
WHERE t1.col > tt.col
ORDER BY col)

FROM tt ) D(col, diff)
WHERE D.diff >= 10

[Antonio Policelli]

Thank you Itzik. You'r articles are always so good.

AP

"Itzik Ben-Gan" <REMOVETH...@solidqualitylearning.com> wrote in message news:<#4cs13DqCHA.1776@TK2MSFTNGP09>...

[Itzik Ben-Gan]

Cheers Antonio, I appreciate this!