How to find the boundaries of groups of contiguous sequential numbers?

by

As mentioned in the comments this is a classic gaps and islands problem.

A solution popularized by Itzik Ben Gan is to use the fact that ROW_NUMBER() OVER (ORDER BY number) - number remains constant within an “island” and cannot appear in multiple islands.

WITH T
     AS (SELECT ROW_NUMBER() OVER (ORDER BY number) - number AS Grp,
                number
         FROM   mytable
         WHERE  status = 0)
SELECT MIN(number) AS [From],
       MAX(number) AS [To]
FROM   T
GROUP  BY Grp
ORDER  BY MIN(number)

NB: If number is not guaranteed to be unique replace ROW_NUMBER with DENSE_RANK in the code above.

More Related Contents:

  1. How to split a comma-separated value to columns
  2. Using T-SQL, return nth delimited element from a string
  3. How to get a float result by dividing two integer values using T-SQL?
  4. Get first day of week in SQL Server
  5. How to add Active Directory user group as login in SQL Server
  6. SQL Server add auto increment primary key to existing table
  7. How to delete large data of table in SQL without log?
  8. Confused about UPDLOCK, HOLDLOCK
  9. Retrieve column definition for stored procedure result set
  10. Change Notification with Sql Server 2008
  11. Do while loop in SQL Server 2008
  12. Query times out when executed from web, but super-fast when executed from SSMS
  13. TSQL: How to convert local time to UTC? (SQL Server 2008)
  14. Regular Expressions in SQL Server servers?
  15. Unique constraint on multiple columns
  16. Transpose rows and columns with no aggregate
  17. How to find the port for MS SQL Server 2008?
  18. How to convert number of minutes to hh:mm format in TSQL?
  19. SQL Server Configuration Manager cannot be found
  20. SQL Server recursive query
  21. Temporarily disable all foreign key constraints
  22. How do I move a table into a schema in T-SQL
  23. nvarchar(max) vs NText
  24. How to display two digits after decimal point in SQL Server
  25. What are row, page and table locks? And when they are acquired?
  26. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  27. Error converting data types when importing from Excel to SQL Server 2008
  28. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
  29. SQL Server 2008 Spatial: find a point in polygon
  30. Implementing one-to-zero-or-one relation in SQL Server

Leave a Comment