CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2014
Shahbazsaeed38
Shahbazsaeed38

Reputation: 21

Find the Missing Key ID or Numbers from a Column values

Need to find the missing numbers which have been deleted or a Column does not have yet.

For example:

i have a table Named Person have Columns [PersonID] [PersonName]

[PersonID] is primary and incremented Number e.g. From 1 to N.

PersonID            PersonName
1001                       ABC
1002                       ABC
1003                       XYZ
1004                       MNO
1006                       ABC
1008                       MNO
1009                       ABC
1010                       ABC
1011                       XYZ
1014                       ABC
1015                       ABC
1016                       XYZ
1017                       MNO

In the given table ,there are some missing numbers in Column PersonID like

1005
1007
1012 
1013

Need to find the missing Numbers only.

Note: There are more than 20 million records in my table. So please suggest a faster method to find the desired numbers.

Upvotes: 1

Views: 409

Answers (3)

Shahbazsaeed38
Shahbazsaeed38

Reputation: 21

Thanks to all of you who supported and share some points. I have found the way to find the Missing using ROWNUMBER().

 SELECT
 NOTEXIST  FROM  ( 
 SELECT  ROW_NUMBER() OVER (ORDER BY PERSONID) NOTEXIST ,PERSONID  FROM  #A )  T
 WHERE NOTEXIST NOT IN  ( SELECT PERSONID  FROM  PERSONID )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The simplest way is to get ranges. You can do this with lead():

select personid + 1, next_personid - 1 as end_range,
       next_personid - personid - 1 as num_missing
from (select t.*,
             lead(personid) over (order by personid) as next_personid
      from t
     ) t
where next_personid <> personid + 1;

If you still want the list of ids, you can expand out the ranges, but that depends on the database.

In SQL Server 2008, this is much more performance intensive, but you can do it:

select personid + 1, tnext.personid - 1 as end_range,
       text.personid - personid - 1 as num_missing
from t cross apply
     (select top (1) t2.person_id
      from t t2
      where t2.personid > t.person_id
      order by t2.personid asc
     ) tnext
where tnext.personid <> personid + 1;

Upvotes: 0

Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

Create another table and populate all the numbers between Min and Max ranges of PersonID. Do an anti join (Left/right) to get the list of numbers missing.

select * from NewIDTable a
left join OriginalTable b  on a.PersonID=b.PersonID
where b.Personid is null

Upvotes: 0

Related Questions