You have a table with a series of numbers that has gaps in the series.

You want to find these gaps.

Utilize the LEAD function in order to compare the next row with the current row to look for a gap:

CREATE TABLE #Gaps (col1 INTEGER PRIMARY KEY CLUSTERED); INSERT INTO #Gaps (col1)// w w w . de m o 2 s . c o m VALUES (1), (2), (3), (50), (51), (52), (53), (54), (55), (100), (101), (102), (500), (950), (951), (952), (954); -- Compare the value of the current row to the next row. -- If > 1, then there is a gap. WITH cte AS ( SELECT col1 AS CurrentRow, LEAD(col1, 1, NULL) OVER (ORDER BY col1) AS NextRow FROM #Gaps ) SELECT cte.CurrentRow + 1 AS [Start of Gap], cte.NextRow - 1 AS [End of Gap] FROM cte WHERE cte.NextRow - cte.CurrentRow > 1;

This query returns the following result set:

Start of Gap End of Gap ------------ ----------- 4 49 56 99 103 499 501 949 953 953

The LEAD function works in a similar manner to the LAG function, which was covered in the previous recipe.

In this example, a table is created that has gaps in the column.

The table is then queried, comparing the value in the current row to the value in the next row.

If the difference is greater than 1, then a gap exists and is returned in the result set.

To explain this in further detail, let's look at all of the rows, with the next row being returned:

SELECT col1 AS CurrentRow, LEAD(col1, 1, NULL) OVER (ORDER BY col1) AS NextRow FROM #Gaps;

This query returns the following result set:

CurrentRow NextRow ----------- ------- 1 2 2 3 3 50 50 51 51 52 52 53 53 54 54 55 55 100 100 101 101 102 102 500 500 950 950 951 951 952 952 954 954 NULL

For the current row of 1, we can see that the next value for this column is 2. For the current row value of 2, the next value is 3. For the current row value of 3, the next value is 50.

At this point, we have a gap.

Since we have the values of 3 and 50, the gap is from 4 through 49-or, as is coded in the first query, CurrentRow+1 to NextRow-1. Adding the WHERE clause for where the difference is greater than 1 results in only the rows with a gap being returned.

PreviousNext- SQL Server Sorting Rows into Buckets
- SQL Server Grouping Logically Consecutive Rows Together
- SQL Server Accessing Values from Other Rows
- SQL Server Finding Gaps in a Sequence of Numbers
- SQL Server Accessing the First or Last Value from a Partition
- SQL Server Calculating the Relative Position or Rank of a Value within a Set of Values
- SQL Server Calculating Continuous or Discrete Percentiles