Find the missing value

# Thread: Find the missing value

1. Senior Member
Join Date
Dec 1969
Posts
104

## Find the missing value

Hi,<BR><BR>There is a series of numbers 1,2,3,5,8,9,11.<BR>I have to find the missing numbers in the above series i.e. 4,6,7,10 .<BR>Please help.<BR>Thanks

2. Senior Member
Join Date
Dec 1969
Posts
16,931

## RE: Find the missing value

Series of numbers? Could that series be anything? Or will they always be real integer counting numbers starting at 1, ending at an arbituary value?<BR><BR>Craig.

3. Senior Member
Join Date
Dec 1969
Posts
5,955

## Well, that's fairly easy. All you...

...need to do is:<BR><BR>- Sort the array of values<BR>- Loop through them, starting at the second item<BR>- Compare each item with it&#039;s predecessor (MyValue(0) and MyValue(1) - MyValue(1) and MyValue(2) - etc.)<BR>- If the difference between the two is greater than 1, then you know you&#039;re missing at least one item in the series - you can use the difference to find out how many, e.g. if the difference is 3, then you know you&#039;re missing 2 items<BR><BR>Oliver.

4. Senior Member
Join Date
Dec 1969
Posts
104

## sql server Identity field

Thanks for reply.<BR>My series of numbers is sql server identity field.<BR>Thanks

5. Senior Member
Join Date
Dec 1969
Posts
6,476

## Try this query

it will show all missing identity numbers...<BR><BR>NOTE: <BR>Be sure to replace TableName with your table name... <BR>And replace FieldID with your Name of the identity field... <BR><BR><BR>select v1.[FieldID]-min(v1.[FieldID]-v2.[FieldID])+1 StartInt, v1.[FieldID]-1 EndInt, <BR>case when (v1.[FieldID]-min(v1.[FieldID]-v2.[FieldID])+1) = (v1.[FieldID]-1) <BR>then convert(varchar(25), v1.[FieldID]-1) else convert(varchar(25), v1.[FieldID]-min(v1.[FieldID]-v2.[FieldID])+1) + &#039; to &#039; + convert(varchar(25), v1.[FieldID]-1) end <BR>from [TableName] v1 <BR>inner join [TableName] v2 <BR>on v1.[FieldID] &#062; v2.[FieldID] <BR>group by v1.[FieldID] <BR>having min(v1.[FieldID]-v2.[FieldID])-1 &#062; 0

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•