how to find missing sequence or gap in sql

Question

Dear Team, how to find missing sequence or gap in sql

Puneet Kankar · Answer

pls check this link https://stackoverflow.com/questions/10968493/how-to-check-any-missing-number-from-a-series-of-numbers

Pratik Shirse · Answer

thank you all of you for your suggestion.... this is my serial no this n no of data how to fine in this gap number 161630009443 161630009463 161630009464 161630009465 161630009466 161630009446

Midhun T P · Answer

Hi, Try below query - select l.srno + 1 as start from tablename as l left outer join tablename as r on l.srno + 1 = r.srno where r.srno is null ; Reference : https://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/

Manikandan Murugesan · Answer

In SQL Server : SELECT TOP 1 id + 1 FROM mytable mo WHERE NOT EXISTS ( SELECT NULL FROM mytable mi WHERE mi.id = mo.id + 1 ) ORDER BY id For SQL other DB's refer the following Link : https://stackoverflow.com/questions/1312101/how-do-i-find-a-gap-in-running-counter-with-sql

how to find missing sequence or gap in sql

Answers (4)