Missing values between min and max values?

Question

Hi , Help me to solve this. Input: Id 1 4 6 8 10 Expected Output: 1 2 3 4 5 6 7 8 9 10

Jenith Thakkar · Answer

Hi Sai Have You Tried This Solution WITH Missing (missnum, maxid) AS ( SELECT 1 AS missnum, (select max(id) from sequence) UNION ALL SELECT missnum + 1, maxid FROM Missing WHERE missnum ) SELECT missnum FROM Missing

Jaish Mathews · Answer

Are you asking end-end algorithm or any specific issue faced during execution of your algorithm ?

Shweta Lodha · Answer

Hello Sai Reddy, In order to help you, we need more description about your problem. From where are you getting this input? How are you fetching these values? Is it windows forms, wpf or any web technology? Please elaborate bit more.

Jayesh Vaghasiya · Answer

declare @sequence table ( Id int ) insert into @sequence values(3),(4),(6),(8),(10) select l.id+1 as l from @sequence l left join @sequence r on l.id+1=r.id where r.id is null and l.Id union select id from @sequence order by l try this

Yogesh Ghorpade · Answer

create table sequence ( Id int ) insert into sequence values(1),(4),(6),(8),(10) select l.id+1 as l from sequence l left join sequence r on l.id+1=r.id where r.id is null union select sequence.id from sequence order by l Try This

Jenith Thakkar · Answer

Hi Sai try this WITH Missing (missnum, maxid) AS ( SELECT 1 AS missnum, (select max(id) from sequence) UNION ALL SELECT missnum + 1, maxid FROM Missing WHERE missnum ) SELECT missnum FROM Missing

Sai Reddy · Answer

Hi Yogesh, The result is not as expected with your query. I need the values between the minimum and maximum values where the minimum value is 1 and maximum value is 10. The final result should be as follows. 1 2 3 4 5 6 7 8 9 10

Sai Reddy · Answer

Hi, The following is the structure of a table. create table sequence ( Id int ) insert into sequencevalues(1),(4),(6),(8),(10) select * from sequence ---I tried following query but, the result is not as expected. select l.id+1 as l from sequence l left join sequence r on l.id+1=r.id where r.id is null

Missing values between min and max values?

Answers (8)