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.

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

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

`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.
Comments

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

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:

This is pure SQL and should work for all cases:

```
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

Comments

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'.

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.

Works on oracle don't have access to sqlserver at the moment

Works on oracle don't have access to sqlserver at the moment

On MS Sql the same errormessage shows still up.

Extracted the subquery to another CTE, try now.

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

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

Just remove from dual, that's Oracle specific.

indeed: that WORKS like a charm

my 5

my 5

Thanks

my five

Use Below query

```
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)
```

Comments

truly NICE!! My 5

Thanks

Good one. +5

Thanks

+5. Slick.

Thanks

+5

Thanks

as Jörgen Andersson states:

When having a gap > 1 the query does not work. Add 13 to table and 11 will show up and 12 not

When having a gap > 1 the query does not work. Add 13 to table and 11 will show up and 12 not

```
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:

```
select *
from GetMissingNumbers(1)
```

v2

Comments

+5. Excellent.

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.

Then retrieve the missing numbers in the sequence using the below query.

Here is one approach.

Create a table to hold all the numbers from 1 to 10.

```
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.

```
SELECT ID FROM #Sequence
WHERE ID NOT IN
(
SELECT Sequence FROM Table1 WHERE Id = 1
)
```

Comments

Thanks for your support.

But if i am not creating that table is to possible to get result using NOT IN ?

But if i am not creating that table is to possible to get result using NOT IN ?

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.

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.....

You first create a temptable with 1,000,000 rows without an index!!

Happy waiting.....

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.

I agree with you. But NOT IN is better optimized then years ago.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject,
503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada
+1 416-849-8900 x 100

I want to get the missing Sequence ie 2,5,7,9. That's all.