 Hello, welcome to SSU-Detect, so we'll decide and this is continuation of stikos over interview questions and answers. So today we are going to discuss one more interview question and this is very common interview question. So how to write a query to find the missing number of rows. As you can see we have input table that would have only one column and this is integer column. So here we are having data with 1, 3, 4, 5, 6, 9, 10 and 15. So we want to get the missing number of rows up to 15. So as we can see in the output we are having 2, 7, 8, 11, 12, 13 and 14. So these are the missing numbers between 1 to 15 in the input. So how we can write the query to get this output. So we will discuss two different ways to write the query for getting this output. First by using loop and second without using loop. As I am using SQL 2017, you may have another version of SQL server. So first of all I would like to create the table and it would have only one column which is id with the data type integer. So let me execute this statement. So table has been created successfully. Now I just want to insert record on this table which is available in the slide. So we can execute this insert statement as we can see 8 rows affected. It means data have been inserted successfully on this table. So we can write the selected statement to check the data. So we can write select as tick from table name. Now let me execute this selected statement. So as we can see we are having id 1, 3, 4, 5, 6, 9, 10 and 15. So we just want to get data which is missing on this sequence. So first by using loop how we can get the output. So here I just want to declare a variable and it would have the maximum number that is integer. And inside this variable we just want to fetch the maximum number which is in the id. So select maximum id from this table that is part 37. So we just want to declare one more variable and it would have the initial value that would be 1. So this is 1. Now we need to use the loop. So we can write while loop and this while loop will be executed until this max value is greater or equals to iterate i value. Then we can use the begin and end. So this is a syntax for while loop. So what we want to do inside this while loop. So inside this loop we just want to check if this iterate i value is available in the table or not. If value is not available on the table then we want to get those values. Those would be the missing values. So we can use if not exist then we just want to check inside the table. So select one from our table that is part 37 where id is equals to iterate i. So if value is not available in the table then we just want to get those values. So we can directly write select iterate i and after this we just want to increase the value of this i. So we can set iterate i equals to iterate i plus 1. Now I just want to execute this statement to check the output. So as we can see we are having 278 but we are getting in different outputs. So here we just want to initialize one more table that would be going to have the missing values. So we can declare iterate missing values. This is the table and it would have only one column that is id and data type would be integer. And here inside this we just want to insert the values in the iterate missing table. Looks good. Now we just want to check the data in the iterate missing table. So select a stick from this iterate missing table. Now let me execute this to check the output. Now we can execute and we can see 278, 11, 12, 13 and 14. Now we can compare with the output. So we are getting 278, 11, 12, 13 and 14. So both are same. So I hope you have understand how we can get the output by using loop. If we do not want to use the loop, so how we can write the query for getting this output. So here we just want to use the recursive CT. So how we can use. So this is syntax for CT. So we have to write with then the CT as then inside this parenthesis we can write the code. So here first we just want to get the maximum id value from the table. So this is inside this CT. So maximum id from the table. So how we want to get the output from the CT. Here we just want to create the sequence number up to the maximum value which is 15. So it would be going to generate the sequence number from starting point is 1, then up to 15. Then we just want to use the not in which is our existing table. So how we can get this. So this would be the maximum value and we can put a column name as max value. Then we just want to create one more CT and inside this CT we just want to use the recursive CT. So it would be the CT to then as then inside this parenthesis we just want to use the logic. So first we just want to get the minimum value from this part 37 table. So that would be the starting value. So minimum id from part 37 and we can put a alias with the id. Then we just want to use the union all we want to use the CT1 with the CT2. So select from CT. So this is our first CT. So we can put alias with a and we want to use the inner join with the CT2 which is our current CT. So it would be the recursive CT. So how it will work put alias with b on a dot max value should be greater than b dot that is id. So this is the condition and in the selected statement we just want to increase the value by 1. So how we can? So we can use b dot id plus 1. So first of all it will be going to get the 1 from this minimum id then it will be going to use this loop and this is the maximum value. So it will be going to check until this maximum id will not going to be reached. So if this maximum value that is 15 that is greater than 1 so condition is satisfied. So b dot id that is 1 it will be going to increased by 1 that is 2. So this is going to second value and next time it will be going to check for first and second that is also less than 15. So this loop will be exist and it will be going to executed until this maximum value that is 15 is not going to be reached. So this recursive CT will be executed. Now we just want to check the values in this CT2. So select a stick from CT2. Now it should have the value from 1 to 15. Now let me execute and as we can see 1 2 3 4 5 6 7 8 9 10 and 15. So we are having all the values from 1 to 15. Now we just want to use the where id not in our table that is select a stick from our table that is part 37. Now it will be going to get only those values which is missing. So as we can see 2 7 8 11 13 13 14. It seems to be very complex but if you will understand step by step then it is very easy. So I hope you have understand how we can get the missing number of rows by using loop and without using loop. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.