Missing Sequence Number in SQL | Recursive CTE | Generate Sequence Number | SQL Interview Q&A

;WITH Emp_CTE (ID) AS
(
SELECT 1 -- Anchor query
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) 14 -- Recursive Query
)
SELECT EC.ID AS Missing_Sequence FROM Emp_CTE EC
LEFT JOIN tblEmployee E
ON EC.ID = E.EmpID
WHERE E.EmpID IS NULL
Option (MAXRECURSION 0) -- MAXRECURSION Query Hint
;WITH Emp_CTE (ID) AS
(
SELECT 1 -- Anchor query
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) 14 -- Recursive Query
)
SELECT * FROM Emp_CTE
-- PART 2
CREATE TABLE tblEmployee1 (EmpID INT)
INSERT tblEmployee1 values (51000),(51001),(51004),(51009),(51013),(51017),(51020),(51025),(51026)
SELECT * FROM tblEmployee1
DECLARE @StartID INT, @EndID INT;
SELECT @StartID = MIN(EmpID) FROM tblEmployee1
SELECT @EndID = MAX(EmpID) FROM tblEmployee1
;WITH Emp_CTE (ID) AS
(
SELECT @StartID
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) @EndID
)
SELECT EC.ID AS Missing_Sequence FROM Emp_CTE EC
LEFT JOIN tblEmployee1 E
ON EC.ID = E.EmpID
WHERE E.EmpID IS NULL
Option (MAXRECURSION 0)
*/
V22: Comma Separated values to multiple rows | string_split() | SQL Interview Q&A | CROSS APPLY
• Comma Separated values...
V21: Comma Separated values in SQL | XML PATH | SQL to XML Format | Row Tag| Root Tag | SQL Interview Q&A
• Comma Separated values...
This channel would provide new videos on SQL, ETL and Data warehouse concepts. I would create a separate play list for SQL Tutorials for beginners, advanced concepts, Interview questions and data warehousing concepts
Feel free to drop me an email at [e-mail] in case of any queries you have in SQL or clarifications on the videos I have done :)
Blog: sqlwithravimar...
Video 12: Import Tables from one Server to another Using SSMS | Import tables using SQL Query in SSMS --* • Import Tables from one...
Blog: sqlwithravimar...
Video 11: Table Variables in SQL Server | Pound Tables | Table Variable in SQL | Table Variable in Tempdb --* • Table Variables in SQL...
Blog: sqlwithravimar...
Video 10: Quick Practical onTemporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table--* • Quick Practical on Tem...
Blog: sqlwithravimar...
Video 9: Temporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table -- * • Temporary tables in SQ...
Blog: sqlwithravimar...
Video 8: Running total & Avg in SQL | Cumulative Sum & Avg in SQL | Calculating running total & Avg in SQL -- * • Running total & Avg in...
sqlwithravimar...
Video 7 Title: Length Vs DataLength Vs ColumnLength | Len() | DataLength() | Col_Length | Column length in SQL -- * • Length Vs DataLength V...
sqlwithravimar...
Video 6 Title: Tricky CASE Statement Interview Questions | CASE WHEN | WHEN Clause | CASE Statement in SQL -- * • Tricky CASE Statement ...
sqlwithravimar...
Video 5 Title: Delete Duplicates in SQL by retaining one Unique record | Row Duplicates | Business Key Duplicates -- * • Delete Duplicates in S...
sqlwithravimar...
Video 4 Title: Difference between TRUNCATE, DELETE and DROP -- * • Difference between TRU...
sqlwithravimar...
Video 3 Title: Sorting in SQL | Order By | Conditional Sorting| Unusual Sorting -- * • Sorting in SQL | Order...
sqlwithravimar...
Video 2 Title: Difference between Primary Key and Unique Key -- * • Difference between Pri...
sqlwithravimar...
Video 1 Title: Logical Processing of SELECT Statement -- * • Logical Processing of ...
sqlwithravimar...
Blog: sqlwithravimar...
Facebook Page:
/ sql-with-ravimartha-10...
/ etl-dwh-testing-100494...
RUclips Channel:
/ @sqlwithravimartha6357

[@mahvishansari989]

Everything is explained so well and right selection of query. I was looking for such a channel like yours to master SQL. Great work sir !

[@sqlwithravimartha6357]

You are most welcome

[@maheshd1838]

Great sir
U explained very well
Thx for uploading such videos, it would be helpfull a lot..

[@sqlwithravimartha6357]

It's my pleasure

[@user-ih3fn6qu6e]

Hi Sir if it is possible try to cover all interview questions and answers.

[@sqlwithravimartha6357]

sure

[@gucheino]

Hi there, I want to know how you can check the ID sequence in a table that has not been broken along an extensive database; I am talking about 20.000 ids. Thanks, For example, I want to know how I can check that the id=18610 until the id=19019 are consecutive and there are no missing numbers in the sequence.

[@sqlwithravimartha6357]

Pretty much similar, select the records from the table where id >= 18610 and id

[@kiranpatil4968]

[English], [French] how to fetch only english & French with sql removing bracket

[@sqlwithravimartha6357]

One easy way to use REPLACE() function twice one for [ and one for ].

[@Saurabh-ix5bx]

So, if the sequence is 2, 5, 8, 17, 20, 23, 29, 41..how you will do that? there should be a login to find the missing number or pattern..can you please guide ?

[@sqlwithravimartha6357]

you can just generate the list of numbers between min and max of the series and do a left join to get the missing numbers. e.g. in your question generate the number series between 2 and 41.