Sql 2005 server will find the series in quantity

Question

I have data in the following format

89279 89280 89281 89282 89283 89284 89285 89286 89287 89288 160447 160448 160449 160450 160451 160452 160453 160454 160455 160456 160457 160458 

I want o / p in the following format in SQL Server 2005

 89279 89288 10 160447 160458 12 

Answer 1

The standard query is "spaces and islands". SQL Fiddle

 WITH T AS (SELECT *, YourColumn - DENSE_RANK() OVER (ORDER BY YourColumn) AS Grp FROM YourTable) SELECT MIN(YourColumn), MAX(YourColumn), COUNT(YourColumn) FROM T GROUP BY Grp