Select unbroken sequences of numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select unbroken sequences of numbers

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2011-06-02 : 11:46:02

Hi,

I would find it helpful to have a query that searched a table looking at a particular integer field and returned only those rows which formed an unbroken sequence of numbers for this particular field.

So, for example if the field in question was an id that had the following values over different rows:

1, 2, 3, 5, 7, 9, 10, 11, 12, 13, 15

I'd want a query that returned rows with the following ids:

1, 2, 3, 9, 10, 11, 12, 13

To add interest to the task, the field in question is actually an nvarchar and not an integer, but it only contains integer values in this particular instance.

Doable?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-02 : 12:00:07

Here's a thing on finding gaps but yours is similar
http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html

another way which should just give the starting value for sequences

select t1.fld
from tbl t1
join tbl t2
on t1.fld = t2.fld-1
left join tbl t3
on t1.fld = t3.fld+1
where t3.fld is null
order by t1.fld

to give sequences

select distinct t1.fld
from tbl t1
join tbl t2
on t1.fld = t2.fld-1
or t1.fld = t2.fld+1
order by t1.fld

The datatype should be implitely converted as long as you always have an integer somewhere in the expression.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-02 : 12:04:26

Proof of concept:

DECLARE @t TABLE(i NVARCHAR(10) NOT NULL)

;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<15)
INSERT @t SELECT CAST(n AS NVARCHAR) FROM n

DELETE @t WHERE i IN(4,6,8,14)

-- use only the following for actual table, change @t to correct table name
;WITH c(i,r) AS (SELECT i, ROW_NUMBER() OVER (ORDER BY CAST(i AS INT))-i FROM @t)
SELECT i FROM c WHERE r IN (SELECT r FROM c GROUP BY r HAVING COUNT(*)>1)

mattt
Posting Yak Master

194 Posts

Posted - 2011-06-02 : 12:14:48

Brilliant, thanks guys.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-02 : 12:25:23

DECLARE @Table TABLE (ID int)
INSERT INTO @table
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 5 UNION
SELECT 7 UNION
SELECT 9 UNION
SELECT 10 UNION
SELECT 11 UNION
SELECT 12 UNION
SELECT 13 UNION
SELECT 15

;with startingPoints as

(
select id,row_number() over(order by id) as rownum
from @table t1
where not exists
(select * from @table t2 where t2.id = t1.id -1
)
and exists
(select * from @table t2 where t2.id = t1.id+1
)
)

, endPoints
as

(
select id ,row_number() over(order by id) as rownum
from @table t1
where not exists
(select * from @table t2 where t2.id = t1.id +1
)
and exists
(select * from @table t2 where t2.id = t1.id -1
)

)

select distinct t.id
from startingpoints s
inner join endpoints e on e.rownum = s.rownum

inner join @table t
on t.id between s.id and e.id

Jim

Blatantly stolen from Itzik Ben-Gan INSIDE MICROSOFT SEQUEL SERVER 2008: t-SQL Querying

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources