Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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 23:46pm
Edited 14-Nov-12 15:24pm
v3
Comments
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: bad
good
Please Sign up or sign in to vote.

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)
  Permalink  
v5
Comments
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'.
Jörgen Andersson at 14-Nov-12 6:07am
   
Forgot parentheses around the subselect.
Works on oracle don't have access to sqlserver at the moment
digimanus at 14-Nov-12 6:28am
   
On MS Sql the same errormessage shows still up.
Jörgen Andersson at 14-Nov-12 7:13am
   
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
Jörgen Andersson at 14-Nov-12 7:25am
   
Just remove from dual, that's Oracle specific.
digimanus at 14-Nov-12 7:38am
   
indeed: that WORKS like a charm
 
my 5
Jörgen Andersson at 14-Nov-12 7:45am
   
Thanks
Andrew Cherednik at 14-Nov-12 20:25pm
   
my five
Rate this: bad
good
Please Sign up or sign in to vote.

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)
  Permalink  
v2
Comments
Marcus Kramer at 13-Nov-12 9:39am
   
+5. Excellent.
Rate this: bad
good
Please Sign up or sign in to vote.

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)
  Permalink  
Comments
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
Jörgen Andersson at 14-Nov-12 4:20am
   
+5
Shanalal Kasim at 14-Nov-12 4:21am
   
Thanks
digimanus at 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: bad
good
Please Sign up or sign in to vote.

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
)
  Permalink  
Comments
biswarup88 at 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__ 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: bad
good
Please Sign up or sign in to vote.

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

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

  Print Answers RSS
0 OriginalGriff 515
1 Maciej Los 340
2 Richard MacCutchan 265
3 BillWoodruff 225
4 Mathew Soji 155
0 OriginalGriff 8,804
1 Sergey Alexandrovich Kryukov 7,457
2 DamithSL 5,689
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411028.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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