Obtain the missing number

Question

Possible Duplicate:
Find the smallest unused number in SQL Server

I have this table in Sql server

ID |  LetterID | LetterName

ID => int and identity

LetterID => int and unique and NotNull

LetterName => string

LetterID fill from my C# application and user set number for it.for example 1,2,3,4,5,...,100,.. (increment an unit for each row) and now my LetterID is 100 but Sometimes user delete one row from table for example delete row where LetterID is 50, now for insert new row (in application) I suggested to him LetterID chose 50, How can I get the missing numbers from table?

Are we worried about multi-user and locking here? i.e. do you have to lock that 50 from other users? — Bob Vale, Aug 22 '12 at 11:38
Why do you want to fill those gaps? – Tim Schmelter => Yes — hamid reza mansouri, Aug 22 '12 at 11:49
@hamidrezamansouri - Tim's question was **why?** The answer "Yes" doesn't fit. And if you want to reply to someone, start your comment with `@`, their username and a space. They'll get notified of your reply. — Damien_The_Unbeliever, Aug 22 '12 at 13:34

score 1 · Answer 1 · answered Aug 22 '12 at 11:40

1

var output =  Enumerable.Range(1, list.Max(item => item.LetterID))
          .Except(list.Select(item => item.LetterID))

answered Aug 22 '12 at 11:40

cuongle

74,024
28
151
206

Can you please explain further about this source? – hamid reza mansouri Aug 22 '12 at 11:57
this code Except two list.is code optimize for high row? – hamid reza mansouri Aug 22 '12 at 13:02
@hamidrezamansouri: this source use LINQ to Entities – cuongle Aug 22 '12 at 15:47

score 0 · Answer 2 · answered Aug 22 '12 at 11:38

0

select t1.ID, t1.LetterID-1 
from yourtable t1
    left join yourtable t2 
on t1.LetterID = t2.LetterID+1
and t1.ID = t2.ID
where t2.ID is null
and t1.LetterID>(select MIN(LetterID) from yourtable where ID = t1.ID)

answered Aug 22 '12 at 11:38

podiluska

50,950
7
98
104

score 0 · Answer 3 · answered Aug 22 '12 at 12:05

0

;with cte as
(
  select max(LetterID) as id from your_table
  union all
  select id-1 from cte where id>1
)
select cte.id as LetterID
from cte
left join your_table yt on cte.id=yt.LetterID
where yt.id is null
order by cte.id

answered Aug 22 '12 at 12:05

Dumitrescu Bogdan

7,127
2
23
31

score 0 · Accepted Answer · answered Aug 22 '12 at 13:27

0

get the blank/missing row

SELECT TOP 1 x.LetterID +1 
FROM tablename x
WHERE NOT EXISTS(SELECT * FROM tablename xx WHERE xx.LetterID  = x.LetterID  + 1)
ORDER BY x.LetterID

answered Aug 22 '12 at 13:27

NG.

5,695
2
19
30

Obtain the missing number

4 Answers4