How to find a gap in the table between min and max value using pure SQL

Question

4.27/5 (4 votes)

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

SQL

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

sahabiswarup

Updated 14-Nov-12 14:24pm

chaau

v3

Add a Solution

Comments

Shanalal Kasim 13-Nov-12 4:55am

The question is not clear. provide more details.

Herman<T>.Instance 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

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

5 solutions

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.

SQL

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.

SQL

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

Posted 12-Nov-12 23:03pm

__TR__

Comments

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

Herman<T>.Instance 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.

Herman<T>.Instance 13-Nov-12 5:41am

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

Add a Solution

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

Jörgen Andersson · Accepted Answer · 2012-11-13T22:35:00

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:

SQL

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)

Posted 13-Nov-12 22:35pm

Jörgen Andersson

Updated 14-Nov-12 1:25am

v5

Comments

Herman<T>.Instance 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

Herman<T>.Instance 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.

Herman<T>.Instance 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.

Herman<T>.Instance 14-Nov-12 7:38am

indeed: that WORKS like a charm

my 5

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

Thanks

chaau 14-Nov-12 20:25pm

my five

Herman<T>.Instance · Accepted Answer · 2012-11-12T23:22:00

SQL

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:

SQL

select *
from GetMissingNumbers(1)

Shanalal Kasim · Accepted Answer · 2012-11-12T23:51:00

Solution 3

Use Below query

SQL

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)

Posted 12-Nov-12 23:51pm

Shanalal Kasim

Comments

Herman<T>.Instance 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

fjdiewornncalwe 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

Herman<T>.Instance 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

sahabiswarup · Accepted Answer · 2012-11-14T22:23:00

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.

Posted 14-Nov-12 22:23pm

sahabiswarup

Updated 16-Nov-12 18:35pm

v2

How to find a gap in the table between min and max value using pure SQL

5 solutions

Solution 4

Solution 2

Solution 3

Solution 1

Solution 5

Add your solution here

Preview 0

How to find a gap in the table between min and max value using pure SQL

5 solutions

Solution 4

Solution 2

Solution 3

Solution 1

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us