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 22:46pm
Edited 14-Nov-12 14: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 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
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.

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 457
1 OriginalGriff 380
2 Shai Vashdi 335
3 Peter Leow 171
4 Emre Ataseven 165
0 Sergey Alexandrovich Kryukov 8,944
1 OriginalGriff 5,280
2 Peter Leow 3,985
3 Maciej Los 3,535
4 Abhinav S 3,218


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