-4

I have int datatype column as No, and want to find those No number which are missing in table.

For example: 1,2,3,7,8,9

Ans = 4,5,6

Thanks in advance.

Parag Meshram
  • 8,281
  • 10
  • 52
  • 88
Lochan
  • 37
  • 1
  • 6

3 Answers3

2

Just in plain sql:

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)

You can try it on Sqlfiddle

Ertunç
  • 819
  • 1
  • 9
  • 21
1

Try Following -

DECLARE @TABLE TABLE (NUM INT)

INSERT INTO @TABLE
VALUES
 (1)
,(2)
,(3)
,(7)
,(8)
,(9)

DECLARE @MIN INT
DECLARE @MAX INT

SELECT @MIN = MIN(NUM) + 1, @MAX = MAX(NUM) - 1 FROM @TABLE

CREATE TABLE #TMP (FIELD_NO INT)
WHILE @MIN <= @MAX
BEGIN
   IF NOT EXISTS (SELECT * FROM @TABLE WHERE NUM = @MIN)
      INSERT INTO #TMP (FIELD_NO) VALUES (@MIN)
   SET @MIN = @MIN + 1
END

SELECT * FROM #TMP

DROP TABLE #TMP
Parag Meshram
  • 8,281
  • 10
  • 52
  • 88
  • MIN should be initiated as MIN+1 and MAX should be initiated as MAX-1 before entering the loop, as we know that MIN and MAX already exist, so no point checking those 2 values again in the loop – Seasoned Oct 22 '12 at 05:56
  • Thanks Parag , it is useful but there is a problem in our case min and max variable derived from another select statement, so pls tell me how to execute a select statement loop – Lochan Oct 22 '12 at 06:33
  • @Seasoned - Initializing MIN = MIN + 1 is OK but initializing MAX = MAX + 1 would give a non desired result. – Parag Meshram Oct 22 '12 at 06:51
  • MIN should be MIN+1, but for MAX i mentioned it should be MAX-1 i.e. MAX minus 1 and not plus 1 – Seasoned Oct 22 '12 at 08:38
1

You can use a table of numbers

Here I use master..spt_values that has the range 0-2047. You can replace that with a numbers table of your own that has enough values.

declare @T table
(
  Number int
);

insert into @T values (1),(2),(3),(7),(8),(9);

with MinMax as
(
  select min(Number) as MinNumber,
         max(Number) as MaxNumber
  from @T
),
Number as
(
  select N.Number
  from master..spt_values as N
    inner join MinMax as MM
      on N.number between MM.MinNumber and MM.MaxNumber
  where N.type = 'P' 
)
select Number
from Number as N
where not exists
  (
    select *
    from @T as T
    where T.Number = N.number
  )
order by N.number
Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281