12,447,242 members (56,260 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
Updated 14-Nov-12 14:24pm
v3
Shanalal Kasim 13-Nov-12 4:55am

The question is not clear. provide more details.
digimanus 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 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 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'.
Jörgen Andersson 14-Nov-12 6:07am

Forgot parentheses around the subselect.
Works on oracle don't have access to sqlserver at the moment
digimanus 14-Nov-12 6:28am

On MS Sql the same errormessage shows still up.
Jörgen Andersson 14-Nov-12 7:13am

Extracted the subquery to another CTE, try now.
digimanus 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
Jörgen Andersson 14-Nov-12 7:25am

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

indeed: that WORKS like a charm

my 5
Jörgen Andersson 14-Nov-12 7:45am

Thanks
Andrew Cherednik 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 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 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
Marcus Kramer 13-Nov-12 9:39am

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

Thanks
Jörgen Andersson 14-Nov-12 4:20am

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

Thanks
digimanus 14-Nov-12 5:45am

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
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 13-Nov-12 5:05am

Thanks for your support.
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.
digimanus 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.
digimanus 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

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

Top Experts
Last 24hrsThis month
 Richard Deeming 335 OriginalGriff 335 ppolymorphe 275 CPallini 130 The Praveen Singh 85
 OriginalGriff 5,623 Karthik Bangalore 3,290 ppolymorphe 3,187 Maciej Los 2,832 Richard Deeming 2,605

Advertise | Privacy | Mobile
Web02 | 2.8.160811.3 | Last Updated 19 Nov 2012