Missing values between min and max values?

Sai Reddy

8y796

Hi , Help me to solve this.

Input:

Expected Output:

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar8y ago
Accepted 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 < maxid

)

SELECT missnum

FROM Missing
0
Jayesh Vaghasiya8y ago
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 < (select max(id) from @sequence)
union select id from @sequence
order by l

try this
0
Sai Reddy8y ago
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

0
Yogesh Ghorpade8y ago
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
0
Jenith Thakkar8y ago
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 < maxid
)
SELECT missnum
FROM Missing
+1
Sai Reddy8y ago
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

-2
Jaish Mathews8y ago
Are you asking end-end algorithm or any specific issue faced during execution of your algorithm ?
+2
Shweta Lodha8y ago
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.
+2