11,639,126 members (63,146 online)
Rate this:
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
`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 12-Nov-12 22:46pm
Edited 14-Nov-12 14:24pm
v3
Shanalal Kasim at 13-Nov-12 4:55am

The question is not clear. provide more details.
digimanus at 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
biswarup88 at 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.

Rate this:

## 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:
```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
digimanus at 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.
digimanus at 14-Nov-12 6:28am

On MS Sql the same errormessage shows still up.

Extracted the subquery to another CTE, try now.
digimanus at 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

Just remove from dual, that's Oracle specific.
digimanus at 14-Nov-12 7:38am

indeed: that WORKS like a charm

my 5

Thanks
Andrew Cherednik at 14-Nov-12 20:25pm

my five
Rate this:

## Solution 2

```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
Marcus Kramer at 13-Nov-12 9:39am

+5. Excellent.
Rate this:

## Solution 3

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)```
digimanus at 13-Nov-12 7:36am

truly NICE!! My 5
Shanalal Kasim at 13-Nov-12 9:34am

Thanks
__TR__ at 13-Nov-12 7:59am

Good one. +5
Shanalal Kasim at 13-Nov-12 9:34am

Thanks
Marcus Kramer at 13-Nov-12 9:39am

+5. Slick.
Shanalal Kasim at 13-Nov-12 9:55am

Thanks

+5
Shanalal Kasim at 14-Nov-12 4:21am

Thanks
digimanus at 14-Nov-12 5:45am

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

## 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.
```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
)```
biswarup88 at 13-Nov-12 5:05am

But if i am not creating that table is to possible to get result using NOT IN ?
__TR__ at 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.
digimanus at 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__ at 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.
digimanus at 13-Nov-12 5:41am

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

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