How to find Missing number in Sequence field using SQL?

Question

I have a table called Student and field name called StudentNumber

Student Table

StudentNumber
-------------
 1
 2 
 3
 4
 5
 8
 10

Expecting output

6
7
9

I tried like below

Declare @trans int;
set @trans = 1;

while(@trans <=50000)
BEGIN
    if((select StudentNumber from [Student] where StudentNumber = @trans) != @trans)
    BEGIN
        print @trans;
    END
END

set @trans = @trans + 1;

maybe loop till the max(number) and check on each iteration if the id exists or not ? — Monah, Nov 25 '17 at 09:09
is the number 50000 fixed all the time? or might be changed? — Monah, Nov 25 '17 at 09:10

score 0 · Answer 1 · edited Nov 25 '17 at 14:40

0

Try like this;

declare @id int
declare @maxStudentNumber int

set @id = 1
select @maxStudentNumber = max(StudentNumber) from Student

create table #MissingIds
(
    id int
)

while @id < @maxStudentNumber
begin
    insert into #MissingIds values(@id)
    set @id = @id + 1
end

select m.id 
from #MissingIds m 
left join Student s 
on m.id = s.StudentNumber 
where s.StudentNumber is null

drop table #MissingIds

edited Nov 25 '17 at 14:40

paparazzo

44,497
23
105
176

answered Nov 25 '17 at 09:10

lucky

12,734
4
24
46

It will not be null. there is no number – Liam neesan Nov 25 '17 at 09:22
@Liamneesan In the case of a `LEFT JOIN`, if there is no matching row in the right-hand table, you get a `NULL` value back for all columns from the right-hand table. See https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join – Matt Gibson Nov 25 '17 at 09:26
What do you mean exactly "It will not be null" ? – lucky Nov 25 '17 at 09:28

score 0 · Answer 2 · answered Nov 25 '17 at 09:24

You could use (SQL Server 2016 and above):

SELECT Number
FROM (select cast([key] as int) + 
      (SELECT MIN(StudentNumber) FROM Students) as number 
      from OPENJSON( '[1' 
      + replicate(',1',(SELECT MAX(StudentNumber) FROM Students)-
                       (SELECT MIN(StudentNumber) FROM Students))+']')) n
LEFT JOIN Students s
  ON n.number = s.StudentNumber
WHERE s.StudentNumber IS NULL;

DBFiddle Demo

Note: You could exchange first subquery with any other tally number generator. More info: SQL, Auxiliary table of numbers

score 0 · Answer 3 · answered Nov 25 '17 at 09:27

0

it should be like below

Declare @trans int;
set @trans = 1;

while(@trans <=50000)
BEGIN
if NOT EXISTS (select StudentNumber from [Student] where StudentNumber = @trans)
BEGIN
    print @trans;
END
END

set @trans = @trans + 1;

answered Nov 25 '17 at 09:27

Hasmukh Viradiya

174
8

score 0 · Answer 4 · answered Nov 25 '17 at 09:29

You can do the following

;with report as(
   select 1 as missing
   union all
   select missing + 1
   from report
   where missing < @max
)


select *
from report m
where not exists ( select 1 from student s where s.id = m.missing)
option (maxrecursion 0);

Here a working demo

Result

  missing
    6
    7
    9

Hope that this will help you

score 0 · Answer 5 · answered Nov 25 '17 at 09:32

I know you try to solve it iterative, just for sports an idea how to achieve the same using recursive CTE

with missingstudents (StudentNumber)
as (
  select StudentNumber-1 from Students s 
     where not exists (
       select StudentNumber from Students s2 
          where s.StudentNumber-1 = s2.StudentNumber)
UNION ALL
  select StudentNumber-1 from missingstudents s 
     where not exists (
       select StudentNumber from Students s2 
          where s.StudentNumber-1 = s2.StudentNumber)
)
select * from missingstudents

score 0 · Answer 6 · answered Nov 25 '17 at 11:05

0

You can try this:

select m.number from 
(select min(StudentNumber) a,max(StudentNumber) b from Students) c ,master..spt_values M
where c.a <= m.number
and c.b > = m.number
and type ='P'
and m.number not in (select StudentNumber from Students)

answered Nov 25 '17 at 11:05

Anagha

918
1
8
17

How to find Missing number in Sequence field using SQL?

6 Answers6