15,995,388 members
See more:
Here is my table

Table1

ID Sequence
1 1
1 3
1 4
1 6
1 8
1 10

and i want this following output
2
5
7
9

I've tried this
SQL
`SELECT Sequence FROM Table1 WHERE Sequence NOT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10') and ID ='1'`

but it not works.
Posted
Updated 14-Nov-12 14:24pm
v3
Shanalal Kasim 13-Nov-12 4:55am
The question is not clear. provide more details.
Herman<T>.Instance 13-Nov-12 4:58am
It is quite clear why you won't get any results. You say that seqeunce cannot be any nummber in the range 1 to 10, while you want missing numbers. You have to create a function for this. If I have some time spare today I will try to create that function as an example
sahabiswarup 13-Nov-12 4:59am
Please check the Table1 you'll find that the sequence are 1,3,4,6,8,10.
I want to get the missing Sequence ie 2,5,7,9. That's all.

## Solution 4

While Shanalal Kasims solution is cool, it doesn't work when you have a larger gap in the sequence.
This is pure SQL and should work for all cases:
SQL
```WITH M AS (
SELECT  Max(SEQUENCE) s
FROM    table1
WHERE   id = 1
)
,numbers(n) AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Numbers,m
WHERE n + 1 <= s
)
SELECT  n
FROM    numbers
WHERE   n NOT IN (SELECT SEQUENCE FROM table1 WHERE id = 1)```

v5
Herman<T>.Instance 14-Nov-12 5:47am
Hi Jorgen,

I have tested your CTE but I get an error
Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'numbers'.
Forgot parentheses around the subselect.
Herman<T>.Instance 14-Nov-12 6:28am
On MS Sql the same errormessage shows still up.
Extracted the subquery to another CTE, try now.
Herman<T>.Instance 14-Nov-12 7:21am
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dual'.

changing dual to table1 results in 96 rows in stead of 12. That is 8 times the result . My table holds 8 values so for each value it returns 12 rows

## Solution 2

SQL
```CREATE FUNCTION [dbo].[GetMissingNumbers](@ID int)

RETURNS @MissingNr TABLE(MissingNumber int)
AS
begin

DECLARE @minValue int
DECLARE @maxValue int
Declare @checkValue int

select @minValue = min(Sequence) from Table1 where ID = @ID
select @maxValue = max(Sequence) from Table1 where ID = @ID

while @minValue < @maxValue
begin
set @minValue = @minValue+1
select @checkValue = Count(Sequence) from Table1 where ID = @ID and Sequence = @minValue
--print @checkValue
if (ISNULL(@checkValue, 0) = 0)
insert into @MissingNr values (@minValue)
end

return
end```

call to the function is:
SQL
```select *
from GetMissingNumbers(1)```

v2
fjdiewornncalwe 13-Nov-12 9:39am
+5. Excellent.

## Solution 3

Use Below query

SQL
```select Sequence+1 from Table1 where Sequence+1 not in (select Sequence from Table1
where id = 1) and id = 1 and Sequence not in (select max(Sequence) from Table1 where id = 1)```

Herman<T>.Instance 13-Nov-12 7:36am
truly NICE!! My 5
Shanalal Kasim 13-Nov-12 9:34am
Thanks
__TR__ 13-Nov-12 7:59am
Good one. +5
Shanalal Kasim 13-Nov-12 9:34am
Thanks
fjdiewornncalwe 13-Nov-12 9:39am
+5. Slick.

## Solution 1

If I understand correctly you want to retreive the list of numbers which are not in the sequence for a given ID.

Here is one approach.
Create a table to hold all the numbers from 1 to 10.
SQL
```CREATE TABLE #Sequence
(
ID INT
)

DECLARE @MaxValue INT
DECLARE @Value INT
SET @Value = 1
SELECT @MaxValue = MAX(Sequence) FROM #Table1 WHERE ID = 1

WHILE @Value <= @MaxValue
BEGIN

INSERT INTO #Sequence
SELECT @Value

SET @Value = @Value + 1

END```

Then retrieve the missing numbers in the sequence using the below query.
SQL
```SELECT ID FROM #Sequence
WHERE ID NOT IN
(
SELECT Sequence FROM Table1 WHERE Id = 1
)```

sahabiswarup 13-Nov-12 5:05am
But if i am not creating that table is to possible to get result using NOT IN ?
__TR__ 13-Nov-12 5:12am
If you don't want to create a table then you may try writing a function that returns a table with a list of consecutive numbers. The idea is to compare the sequence values against all the numbers and retrieve the once that are missing in the sequence.
Herman<T>.Instance 13-Nov-12 5:23am
for 10 numbers OK, but what if he is checking 1,000,000 rows?
You first create a temptable with 1,000,000 rows without an index!!
Happy waiting.....
__TR__ 13-Nov-12 5:35am
This was one sample approach that came to my mind. I did not take into consideration large volume of data. But i agree with you, for large volumes of data we will need an index. Also i think there would be significant improvement in performance if the NOT IN clause is avoided.
Herman<T>.Instance 13-Nov-12 5:41am
I agree with you. But NOT IN is better optimized then years ago.

## Solution 5

`select sequence from SequenceTable where sequence NOT IN (select sequence FROM Table1 WHERE id=8)`

I've created another table named SequenceTable and stored value from 1 to 10 and then applying the above query.

v2